Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with ISNA, INDEX, MATCH
I am trying to write a formula using the ISNA for finding the name to an ID.
In Sheet 1 cell E2, I tried the following formula...=IF(D20,INDEX(Sheet2!A:F,MATCH('Sheet1' !D2,Sheet2!A:A,0),6),"") On Sheet 2 the ID is in column A but I need the name of the ID that is found in column F. If the ID in column D on Sheet1 is blank, I want the name on Sheet 1 to be blank. If the ID in column D on SHeet 1 is not found in column A on Sheet 2, then I want the result to be "Invalid ID" Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with ISNA, INDEX, MATCH
Begin where you left off:
=IF(D20,IF(ISNUMBER(MATCH(Sheet1!D2,Sheet2!A:A,0) ),INDEX(Sheet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6 ),"Invalid ID"),"") Or =IF(D20,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sh eet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6),"Invalid ID"),"") But since this is on HSeet1, le us simplify to =IF(D20,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sh eet2!A:F,MATCH(D2,Sheet2!A:A,0),6),"Invalid"),"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Nadine" wrote in message ... I am trying to write a formula using the ISNA for finding the name to an ID. In Sheet 1 cell E2, I tried the following formula...=IF(D20,INDEX(Sheet2!A:F,MATCH('Sheet1' !D2,Sheet2!A:A,0),6),"") On Sheet 2 the ID is in column A but I need the name of the ID that is found in column F. If the ID in column D on Sheet1 is blank, I want the name on Sheet 1 to be blank. If the ID in column D on SHeet 1 is not found in column A on Sheet 2, then I want the result to be "Invalid ID" Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with ISNA, INDEX, MATCH
Thank you so much!!!!!
"Bernard Liengme" wrote: Begin where you left off: =IF(D20,IF(ISNUMBER(MATCH(Sheet1!D2,Sheet2!A:A,0) ),INDEX(Sheet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6 ),"Invalid ID"),"") Or =IF(D20,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sh eet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6),"Invalid ID"),"") But since this is on HSeet1, le us simplify to =IF(D20,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sh eet2!A:F,MATCH(D2,Sheet2!A:A,0),6),"Invalid"),"") best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Nadine" wrote in message ... I am trying to write a formula using the ISNA for finding the name to an ID. In Sheet 1 cell E2, I tried the following formula...=IF(D20,INDEX(Sheet2!A:F,MATCH('Sheet1' !D2,Sheet2!A:A,0),6),"") On Sheet 2 the ID is in column A but I need the name of the ID that is found in column F. If the ID in column D on Sheet1 is blank, I want the name on Sheet 1 to be blank. If the ID in column D on SHeet 1 is not found in column A on Sheet 2, then I want the result to be "Invalid ID" Thank you. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISNA with Index | Excel Discussion (Misc queries) | |||
IF?ISNA?MATCH? Function? | Excel Discussion (Misc queries) | |||
If isna match function??? | Excel Discussion (Misc queries) | |||
IsNA(match | Excel Worksheet Functions | |||
IsNA(match | Excel Worksheet Functions |