Excel Issue - ISblank ISerror and vlookup

0
votes

I am having a few issues with this in excel.

First of all i want to wrap this in an ISERROR and can't quite get it correct.

=IF(ISBLANK(P8),(VLOOKUP(Q8,Testdata,3,FALSE)),(VLOOKUP(P8,Testdata,3,FALSE)))

Second i am saying if P8 is blank go query a different column, that seems to work other than the P8 column is a number in a text field, yet the lookup is also a number in a text field. I want it to be text lookup against text but excel is getting confused that its a number?

Q8 is text against text and that works fine, for example Q8 = "abcdefg" and the lookup finds it.

P8 is 123456 and the lookup is text as 123456, the datasource is actually text.

Need help.

2
excelvlookup
to wrap it use IFERROR function – Maddy
i know that is the issue i have, cant quite get the syntax correct – novak73

2 Answers

0
votes

You can wrap this in IFERROR and replace "SomeOtherValue" with another range =IFERROR(IF(ISBLANK(P8),(VLOOKUP(Q8,Testdata,2,FALSE)),(VLOO‌​KUP(P8,Testdata,2,FA‌​LSE))), "SomeOtherValue")

0
votes

Looking up a number stored as text in Excel needs the lookup "value" to be expressed as TEXT so you have to coerce your number into text.

You can convert P8 to text format using `TEXT(P8, "@") so your formula should be:

=IF(ISBLANK(P8),(VLOOKUP(Q8,Testdata,3,FALSE)),(VLOOKUP(TEXT(P8,"@"),Testdata,3,FALSE)))