Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula help comparing colums for matches within a range

    I am trying to compare items between two columns looking for a match.
    If a match is found, then I would like to result to show "purchased" if a match is not found, then the result should show "need to buy". This is what I have come up with so far. Any ideas?


    B5:B10
    Column1 Column2
    555 555 PURCHASED
    555 888 NEED TO BUY
    982 982 PURCHASED
    982 555 NEED TO BUY<-- THIS SHOULD SAY PURCHASED
    982 982 PURCHASED
    982 999 NEED TO BUY

    FORMULA: =IF(B5=TABLE1{COLUMN1},"PURCHASED","NEED TO BUY")

    I am having trouble with the formula looking at the entire column #1 when comparing to column 2.

    Thank you,
    MGTL

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi mgtl

    Here is my take.

    =IF(ISERROR(MATCH(B2,$A$2:$A$6,0)),"Need to Purchase","purchased")

    Hope it helps.
    Cheers
    Geof
    Attached Images Attached Images

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    This may also work .....
    Formula in C5 and copied down is ....
    =IF(ISERROR(VLOOKUP(B5,A$5:A$10,1,FALSE)),"NEED TO BUY","PURCHASED")
    Attached Images Attached Images

  4. #4
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Reply Formula Question

    Quote Originally Posted by geofrichardson View Post
    Hi mgtl

    Here is my take.

    =IF(ISERROR(MATCH(B2,$A$2:$A$6,0)),"Need to Purchase","purchased")

    Hope it helps.
    Cheers
    Geof
    This works beautifully!! Thank you for your help!!

    Cheers to you,

    MGTL

  5. #5
    New Lounger
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you! This works well. I appreciate the help!!

    MGTL

Posting Permissions

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