Need help with ISNA, INDEX, MATCH

N

Nadine

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(D2>0,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.
 
B

Bernard Liengme

Begin where you left off:
=IF(D2>0,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(D2>0,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sheet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6),"Invalid
ID"),"")
But since this is on HSeet1, le us simplify to
=IF(D2>0,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sheet2!A:F,MATCH(D2,Sheet2!A:A,0),6),"Invalid"),"")
best wishes
 
N

Nadine

Thank you so much!!!!!

Bernard Liengme said:
Begin where you left off:
=IF(D2>0,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(D2>0,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sheet2!A:F,MATCH(Sheet1!D2,Sheet2!A:A,0),6),"Invalid
ID"),"")
But since this is on HSeet1, le us simplify to
=IF(D2>0,IF(COUNTIF(Sheet2!A:A,Sheet1!D2),INDEX(Sheet2!A:F,MATCH(D2,Sheet2!A:A,0),6),"Invalid"),"")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
VBA Coding Help for Beginner 0
Mail Merge Help 1
Index Match to ignore blank cells 0
Horizontal and Vertical lookup 2
Match issue 3
Help! Is this possible 2
Index Match Help 6

Top