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

CodePudding user response：

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);"");"")