Vlookup and #N/A

[lynx1971]

Hi everyone,

I have the following Vlookup formula =VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0) and it works just fine, but if the input cell R26 is empty I get a #N/A in the relevant cell. I tried searching for an answer in the help section in Excel itself, but it is returning replies on how to use the help files . While it does not affect the worksheet at all, I'd rather have just an empty cell instead of #N/A.

Thanks in advance,

Lynx

 

[Aladin Akyurek]

Hi everyone,

I have the following Vlookup formula =VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0) and it works just fine, but if the input cell R26 is empty I get a #N/A in the relevant cell. I tried searching for an answer in the help section in Excel itself, but it is returning replies on how to use the help files . While it does not affect the worksheet at all, I'd rather have just an empty cell instead of #N/A.

Thanks in advance,

Lynx

If you are on Excel 2007 or later...

=IFERROR(VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0),"")

If on a previous version...

1) VLOOKUP is expected to return text...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0)))

2) VLOOKUP is expected to return a number...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0)))

 

[RichardMGreen]

Try wrapping an iserror around the vlookup. That way you'll trap all errors not just if R26 is empty. It'll be something like this:-
=if(iserror(VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0)),"",VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0))

 

[Peter_SSs]

if the input cell R26 is empty I get a #N/A

If that is all you are trying to overcome, then I would just use

=IF(R26="","",VLOOKUP(R26,Sheet1!$B$2:$F$28,5,0))

 

[lynx1971]

SOLVED Re: Vlookup and #N/A

Thank you everyone who replied. Will try out your suggestions tomorrow morning when I get to the office.

Cheers,

L

 

[lynx1971]

Ok I've tried using all the formulae above but to no avail. The sheet on which the original formula is located is simply called KIB. I've MS office 2003. What am I doing wrong?

Thanks in advance,

Lynx

 

[grizz]

maybe
=IF(ISNA(VLOOKUP(R26,sheet1!$B$2:$F$28,5,FALSE)=TRUE),"",VLOOKUP(R26,sheet1!$B$2:$F$28,5,FALSE))

 

[Aladin Akyurek]

Ok I've tried using all the formulae above but to no avail. The sheet on which the original formula is located is simply called KIB. I've MS office 2003. What am I doing wrong?

Thanks in advance,

Lynx

Care to post the formula you tried?

 

[lynx1971]

This is the formula I am currently using in cell the cell just adjacent to cell R26:
=VLOOKUP(R26;KIB!$B$2:$F$28;3;0)

It's fine, but if there is no data in the first cell then it shows #N/A.

The data it is trying to locate is based on the table below where the first column are the codes and the 2nd and 3rd columns are the values which are looked up and placed in the desired position.

120 1760ABCD12 6062100000
130 1760ABCD13 6062100001
140 1760ABCD14 6062100002
150 1760ABCD15 6062100003
160 1760ABCD16 6062100004

TIA

 

[RichardMGreen]

Try:-
=if(iserror(VLOOKUP(R26;KIB!$B$2:$F$28;3;0)),"",VLOOKUP(R26;KIB!$B$2:$F$28;3;0))

Replace the "" in the middle with 0 if it helps.