On 2002-03-28 13:24, professor02 wrote:
I have a main list (list1) of numbers (ex. xxx-yyy) that I am comparing to another list of numbers (list2). First, I need to see if any numbers in list2 match any numbers in list1 so I used =IF(D5=VLOOKUP(D5,A$5:A$42,1),"GO","STOP"). The next step is if the first condition is satisfied ("GO"), then I need to compare the corresponding yyy of list2 to list1. Any suggestions would be greatly appreciated.
First of all, the way you are using VLOOKUP will match the largest value that is less than or equal to the value in D5. To make sure you get an exact match, you need to include the 4th argument, which is TRUE or FALSE (or 1 or 0, respectively). The formula I have below is totally redundant, but shows what you're trying to do.
If a VLOOKUP does not find a match (with the 4th argument as False, or zero), it will return #N/A. You can use ISNA to see if the VLOOKUP function returns #N/A (i.e., it doesn't find a match).
So, what the formula below says is:
If the first Lookup doesn't find a match then return "Not Found", else (there was a match) check this number again with vlookup...if it doesn't return a match, then put "Not Found", else put "Found". Like I said, my example is redundant in that it checks the same number twice against the same array. But hopefully it illustrates how to do what you want. Hope it helps. Formula below.
=IF(ISNA(VLOOKUP(B2,Sheet3!$A$1:$A$6,1,0)),"Not Found",IF(ISNA(VLOOKUP(B2,Sheet3!A1:A6,1,0)),"Not Found","Found"))
