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

 

[Peter_SSs]

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

If "the first cell" means R26 then some of the suggested formulas should have stopped you getting a #NA result - unless you were not correctly substituting ";" for "," in the formulas to match your language Excel version.

One thing is that the cell may in fact not be blank but contain some character(s) that you cannot see. In a vacant cell try
=CODE(R26)
or instead of 26 use whatever row number is currently returning #NA.


Can you confirm what sheet name this VLOOKUP formula is on?

Can you confirm that the table being looked up is 3 columns? Which columns?

 

[lynx1971]

Richard when I copy in the formula, it gives displays a formula error and highlights the second VLOOKUP portion.

 

[lynx1971]

Peter I feel like a complete dolt!!! You were right, the formula worked fine after I changed the regional language back to English! Thanks a million, this looks much neater. Have a great day! Cheers.

 

[grizz]

in R27
=IF(ISNA(VLOOKUP(R26,KIB!$B$2:$F$28,2,FALSE)=TRUE),"",VLOOKUP(R26,KIB$B$2:$F$28,2,FALSE))

in R28
=IF(ISNA(VLOOKUP(R26,KIB!$B$2:$F$28,3,FALSE)=TRUE),"",VLOOKUP(R26,KIB$B$2:$F$28,3,FALSE))

then enter your number in R26

 

[Peter_SSs]

No problem.

If you haven't done so already, it might be worth going back through the suggestions and consider which one(s) efficiently best suit your purpose. In relation to the one I suggested, compared to Richard's or grizz's if the issue is that column R is blank then again I say it is simpler and more efficient to just check if column R is blank than do the whole lookup and see if it returns an error.

 

[lynx1971]

Peter, after you highlighted my error, I created two further copies of the workbook and will be using your simpler formula which you'd posted on the 3rd of March to see how it works.

 

[lynx1971]

Peter that simpler IF formula from the 3rd March post worked too. The problem seemed to have been my switching between English and French languages. I'm going to keep this simple version in my workbook, because I have to confess the IF and ISERROR formulae are daunting and I've never used the ISERROR at all. Thank to everyone once again. I'm now off to learn how to use the COLOR formula. Will post if I run into snags. Cheers.