Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding OR to an index match formula
Diddy,
You need to double up your formula, essentially: =IF(ISNA(INDEX(Nccode,MATCH($C2,NCC,0))),IF(ISNA(I NDEX(Nccode,MATCH($C2,NCE,0))),"",INDEX(Nccode,MAT CH($C2,NCE,0))) ,INDEX(Nccode,MATCH($C2,NCC,0))) HTH, Bernie MS Excel MVP "Diddy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding OR to an index match formula
Thank you Bernie :-)
Worked a treat - much neater ! Cheers Diddy "Bernie Deitrick" wrote: Diddy, You need to double up your formula, essentially: =IF(ISNA(INDEX(Nccode,MATCH($C2,NCC,0))),IF(ISNA(I NDEX(Nccode,MATCH($C2,NCE,0))),"",INDEX(Nccode,MAT CH($C2,NCE,0))) ,INDEX(Nccode,MATCH($C2,NCC,0))) HTH, Bernie MS Excel MVP "Diddy" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula INDEX/MATCH | Excel Worksheet Functions | |||
Need Help w/ Index and Match Formula | Excel Worksheet Functions | |||
Formula using INDEX and MATCH | Excel Worksheet Functions | |||
INDEX and MATCH in one formula... | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |