Need help with ISNA, INDEX, MATCH

  • Follow


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.
0
Reply Utf 4/5/2010 6:31:28 PM

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

"Nadine" <Nadine@discussions.microsoft.com> wrote in message 
news:C08BA4AE-FCD8-4113-81EB-BB058430F24B@microsoft.com...
> 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. 

0
Reply Bernard 4/5/2010 7:10:49 PM

Thank you so much!!!!!

"Bernard Liengme" wrote:

> 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
> 
> "Nadine" <Nadine@discussions.microsoft.com> wrote in message 
> news:C08BA4AE-FCD8-4113-81EB-BB058430F24B@microsoft.com...
> > 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. 
> 
> .
> 
0
Reply Utf 4/5/2010 8:38:12 PM

2 Replies
349 Views

(page loaded in 0.016 seconds)


Reply: