Nested IFERROR(IF(VLOOKUP Function Not Working 100%

BeerusDev 0
Viewed 24

I have a multi-sheet workbook where all sheets are being pulled into one. I had my equation working like a charm, but when there was a blank cell, it would return 0. I wanted to customize the text returned to a string, so when I added the IF statement to the equation, it returns the string if the cell is blank, but if the cell contains a value that I want it to return, it only returns FALSE...

Here is my example: =IFERROR(IF(VLOOKUP(A1,’Sheet 1’!2:200,2,FALSE)=0,"No Value"),IFERROR(IF(VLOOKUP(A1,’Sheet 2’!2:200,2,FALSE)=0,"No Value"), “N/A”))

excel-formula excel
1 Answers

I think you Logic is off a bit:

=IF(
    IFERROR(VLOOKUP(A1,'Sheet 1'!2:200,2,FALSE)&"","")<>"",
    VLOOKUP(A1,'Sheet 1'!2:200,2,FALSE),
    IF(
       IFERROR(VLOOKUP(A1,'Sheet 2'!2:200,2,FALSE)&"","")<>"",
       VLOOKUP(A1,'Sheet 2'!2:200,2,FALSE),
       "No Value"
       )
    )

The &"" will force the blank return to become a true blank return.

Now it will check sheet 1 first and if the return is blank or an error we will move to test sheet 2, otherwise it will return the value from the VLOOKUP on Sheet 1.

If sheet 2 return is blank or an error it will return "No Value". Otherwise it will return the value in Sheet 2.

Scott Craner0
Related