IF in combination with VLOOKUP returns in #VALUE

Question

I'm working with an Excel sheet that has 2 sheets. Sheet 1 has all the data with the key in Column 1 and answer/data in Column 3.

There are a lot of rows and this is changing continuously, so one would make this maintenance free.

If a certain row in Sheet 1 has an empty value, sheet 2 will show a '0'. To prevent this from happening, I created the following formula with example information.

Sheet 1 starts on A3 and ends on A300 (answers in column 3 aka C) Record in sheet 1 with empty cell is A10

=IF(VLOOKUP(A10;'Sheet1'!$A$3:$C$300;3;TRUE);VLOOKUP(A10;'Sheet1'!$A$3:$C$300;3;TRUE);"")

So if value of A10 in Sheet 1 in Column 3 = TRUE, get that value and present it else show nothing. This works flawlessly, however, when I update the cell in Sheet1 in C10, with Yes. The VLOOKUP in Sheet2 will return #VALUE. Once I update the value in C10 with random numeric values such as 123456, Sheet2 will show 123456.

Once I remove the IF statement around VLOOKUP the value of Sheet2 will becomes Yes (after I updated Sheet1 C10 back to Yes).

Anyone know if IF(VLOOKUP) works on Text? In my situation it doesn't somehow, but with numbers its fine.

With kind regards, Engineer

Answer 1

You need to change the logic a bit:

• Check if A10 can be found on sheet 1 (can be done many different ways, I've chosen COUNTIFS).
• If found, check that the VLOOKUP does not return 0.

=IF(COUNTIFS(Sheet1!A3:A300;A10)>0;IF(VLOOKUP(A10;Sheet1!A3:C300;3;TRUE)<>0;VLOOKUP(A10;Sheet1!A3:C300;3;TRUE);"");"")