# Vlookup and #N/A

#### lynx1971

##### New Member
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.

Lynx

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

##### MrExcel MVP
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.

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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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

##### New Member
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?

Lynx

#### grizz

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

##### MrExcel MVP
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?

Lynx

Care to post the formula you tried?

#### lynx1971

##### New Member
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

##### Well-known Member
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.

Replies
1
Views
897
Replies
5
Views
397
Replies
5
Views
582
Replies
8
Views
556
Replies
2
Views
2K

1,191,191
Messages
5,985,213
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back