Adding OR to an index match formula

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

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" <Diddy.DeleteThis@discussions.microsoft.com> wrote in message
news:28A160D7-6D7A-48C7-964C-413F695B687C@microsoft.com...
> 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