Need help with ISNA, INDEX, MATCH
in [Functions]
|
Prev: copy excel spread sheet & graph to word doc without formulas
Next: calculate diffence between old salary and new salary
From: Nadine on 5 Apr 2010 14:31 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.
From: Bernard Liengme on 5 Apr 2010 15:10 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(a)discussions.microsoft.com> wrote in message news:C08BA4AE-FCD8-4113-81EB-BB058430F24B(a)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.
From: Nadine on 5 Apr 2010 16:38
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(a)discussions.microsoft.com> wrote in message > news:C08BA4AE-FCD8-4113-81EB-BB058430F24B(a)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. > > . > |