Adding OR to an index match formula

[Diddy]

Hi everyone,

I'm using Index match to return a descriptor code that matches different
items in Column C.
The items and their descriptor codes are named ranges in another worksheet.
Formula in D is
=IF(ISNA(INDEX(Nccode,MATCH($C2,NCC,0))),"",INDEX(Nccode,MATCH($C2,NCC,0)))

My problem is that the workbook I'm working on at the moment is bilingual so
the items are in two languages.
What I'm doing (lazy way) is setting up a second column (E) of index match
with Range NCE instead of NCC. So I've got two columns of descriptor codes
which I concatenate in F using =D2&E2.

There must be a way to add an or into the Index Match to do this in one go!

Could any kind soul help me with this please?

Cheers

Diddy

 

[Bernie Deitrick]

Diddy,

You need to double up your formula, essentially:

=IF(ISNA(INDEX(Nccode,MATCH($C2,NCC,0))),IF(ISNA(INDEX(Nccode,MATCH($C2,NCE,0))),"",INDEX(Nccode,MATCH($C2,NCE,0)))
,INDEX(Nccode,MATCH($C2,NCC,0)))


HTH,
Bernie
MS Excel MVP

 

[Diddy]

Thank you Bernie

Worked a treat - much neater !

Cheers
Diddy