isna in vlookup

[Jane Fischer]

Is there a way to add the isna function to this vlookup? Any help on this would be great.

=IF(B2="WGSTC",VLOOKUP(A2,'\\swfs04v01\fwal1shr\Plant-0\Finance\Other\WGS\[WGS P5 and TC Grinding Cost.xlsx]Sheet1'!$A$3:$D$974,4,0),VLOOKUP(A2,'\\swfs04v01\fwal1shr\Plant-0\Finance\Other\WGS\[WGS P5 and TC Grinding Cost.xlsx]Sheet1'!$A$3:$E$974,5,0))

 

[Jonmo1]

First, the only difference between the 2 vlookups is the column Index (4 or 5).
So that can be simplified a bit to

=VLOOKUP(A2,'\\swfs04v01\fwal1shr\Plant-0\Finance\Other\WGS\[WGS P5 and TC Grinding Cost.xlsx]Sheet1'!$A$3:$E$974,4+(B2 < > "WGSTC"),0)

Now, what version of Excel do you have?
If you have 2007 or higher, you can use

=IFERROR(VLOOKUP(...),"")

If you have 2003 or earlier, you need

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
or
=IF(ISNA(MATCH(B2,'\\path\..etc..'!$A$3:$A$974,0)),"",VLOOKUP(...))


Hope that helps.

 

[Jane Fischer]

Thank you that helps but I am still having problems getting the IsError to work.

 

[Aladin Akyurek]

Thank you that helps but I am still having problems getting the IsError to work.

What kind of data do VLOOKUPs return: Number or text?

 

[Jonmo1]

And which version of Excel are you using?

 

[Jane Fischer]

I am using excel 2007 and the lookup is returning a number.

 

[Jonmo1]

Try

=IFERROR(VLOOKUP(A2,'\\swfs04v01\fwal1shr\Plant-0\Finance\Other\WGS\[WGS P5 and TC Grinding Cost.xlsx]Sheet1'!$A$3:$E$974,4+(B2 < > "WGSTC"),0),"")

 

[Jane Fischer]

Perfect. Thank you very much!