syntax for performing 2 VLOOKUP funcitions in 1 Cell

I need to evaluate two conditions using the VLOOKUP function in one cell.  Looking for the syntax to do so.

Thanks,

Felix

Please disregard setting the VLOOKUP "Range_lookup" to TRUE should work for me in this case.

Thanks,

Felix

Much to my dismay, simply setting the "Range_lookup" value to TRUE in the VLOOKUP function did not work properly in every case.

I tried to nest 2 Vlookup functions in one IF statement but only get a #VALUE! result.  I have also tried experimenting combining the INDEX and MATCH functions together INDEX(Range,MATCH(), as described in the help, which works if I only apply it to one set of lookup arguments(condition).  But does not work if I try to satisfy for the two conditions.  When trying to combine the two INDEX(Range,MATCH() formulas together in an IF statement, I recieve a #N/A. My syntax is mostly likely the culprit, but here are the details on my data structure and what I need it to do:

1) I have a "Specification" table which contains a column named "Product ID", and other columns (irrelevant).

2) I also have a "Sampling Point" table, which contains the only following columns for lookup purposes: "Dynamic Spec", "Static Spec", and "Identity" (in that order).

3) The "Product Id" value in the "Specification" table is either a value in the "Dynamic Spec" column or the "Static Spec" column of the "Sampling Point" table.

4) I have created a new column, "Product ID Sampling Point" in the "Specification" table which needs to evaluate the "Product ID" value, locate that value in either the "Dynamic Spec" or the "Static Spec" columns and return the corresponding "Identity" value from the "Sampling Point" table.

Any help would be greatly appreciated.

Thanks,

Felix

Felix

I hope I have understood. (My testing had your Product ID (lookup value) in A1 and the Sampling Point Table in A4:C50 on the same sheet. (Just change these to suit))

I came up with this, which tests first if the Product ID exists at all using the AND function and ISERROR, if that returns TRUE I return a blank cell, if it doesn't (e.g. one or other finds a match), it determines which one and returns the Identity.

Let me know if it works for you, here is the formula

=IF(AND(ISERROR(VLOOKUP($A$1,$A$4:$A$50,1,FALSE)),ISERROR(VLOOKUP($A$1,$B$4:$B$50,1,FALSE))),"",IF(ISERROR(VLOOKUP($A$1,$A$4:$A$50,1,FALSE)),VLOOKUP($A$1,$B$4:$C$50,2,FALSE),VLOOKUP($A$1,$A$4:$C$50,3,FALSE)))

Nick,

Thanks a million, that worked like a charm. Cheers !!!

Felix