Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Using Conditional statements

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •