-1

=IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big'!$B:$B,1,FALSE),"Project BIG",IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Cran'!$B:$B,1,FALSE),"Project Cranberry",IF(VLOOKUP(AH4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$E:$E,1,FALSE),"Project BIG(Original)",IF(VLOOKUP(AH4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$G:$G,1,FALSE),"Project BIG(New)"," "))))

Above is an excel code I am using to cross check SKUs on other sheets. If the VLOOKUP is found I want the [if_true_value] to state which sheet it's on (i.e. "Project BIG") if VLOOKUP is not found the [if_false_value] runs another VLOOKUP to search another sheet. The final [if_false_value] is " " which I simply wanted a blank cell if the SKU is not found. However I am getting #N/A is the cells.

So my question: Is this a valid way of using VLOOKUP? Is it actually iterating through the true/false conditions to the end of the code? And any suggestions to improve this?

Thanks for your time!

Improve this question
1
  • If it works, it's fine. Though, personally I'd use cell("filename",d4) instead of "hard coding" the name in each Lookup results.
    – P. O.
    Jul 30, 2015 at 18:27

3 Answers 3

Reset to default
0

Wrap each VLookup in NOT(ISERROR()) to achieve what you're looking for:

=IF(NOT(ISERROR(VLOOKUP(A1,sheetname!A:A,1,FALSE))),"some statement","another vlookup")
Improve this answer
0

Much as been already suggested, you need to check if you are returning a result. As you only want to check existance, a VLOOKUP function is a bit of overkill. I would suggest a simpler MATCH function with a check to see if it is returning a row number (found) or error (not found).

=IF(ISNUMBER(MATCH(D4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big'!$B:$B, 0)), "Project BIG",
    IF(ISNUMBER(MATCH(D4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Cran'!$B:$B, 0)), "Project Cranberry",
       IF(ISNUMBER(MATCH(AH4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$E:$E, 0)), "Project BIG(Original)", 
          IF(ISNUMBER(MATCH(AH4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$G:$G, 0)), "Project BIG(New)",
    ""))))

You can keep the line feeds in your formula to help you make more sense of it.

Improve this answer
1
  • I kept the VLOOKUP but did something similar to what you suggested. Thank you!
    – Jo So
    Aug 4, 2015 at 18:37
0 
=IF(ISNA(VLOOKUP($D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big Sku List '!$C:$C,1,FALSE)),
    IF(ISNA(VLOOKUP($D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Cranberry Sku List'!$I:$I,1,FALSE)),
    IF(ISNA(VLOOKUP(AH4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$E:$E,1,FALSE)),
    IF(ISNA(VLOOKUP(AH4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]SMP BIG List'!$G:$G,1,FALSE)),
    "NO","Project BIG(NEW)"),"Project BIG(Original)"),"Project Cranberry"),"Project BIG")

Above is what I came up with.

The ISNA will check the value of VLOOKUP and if it is true (#N/A) it does another VLOOKUP all the way down the chain, through different sheets. Then the "False" statements tell me where the VLOOKUP was found. Verified a few of them manually, I am confident this solution works.

I thank you all and hope this is helpful for the next person!!

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .