IF(ISERROR OR IF(ISERROR THEN...

Options

I've been able to get 2 different formulas to check the two different places I need looked up but I can't seem to get them to 1 combined formula. Can someone help me figure out how to make 1 formula that will check 2 places for an entry and say it's Duplicated or No Match please?

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/13/24 Answer ✓
    Options

    @Amanda Carta

    See how this formula works out for you

    =IF(COUNTIF(Current:Past, = New@row)>0,"Duplicate","No Match")

    If you want to make it a column formula. Based on your sample table.

    =IF(ISBLANK(New@row), "", IF(COUNTIF(Current:Past, =New@row) > 0, "Duplicate", "No Match"))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/13/24 Answer ✓
    Options

    @Amanda Carta

    See how this formula works out for you

    =IF(COUNTIF(Current:Past, = New@row)>0,"Duplicate","No Match")

    If you want to make it a column formula. Based on your sample table.

    =IF(ISBLANK(New@row), "", IF(COUNTIF(Current:Past, =New@row) > 0, "Duplicate", "No Match"))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Amanda Carta
    Options

    I love the new way of looking at it as a count function! That works but… I forgot to mention that Current and Past are on 2 different worksheets. How would I do that?

  • Amanda Carta
    Options

    AH HA!!! I got it! :) Thank you for making me think outside the box. I was so stuck on Vlookup :)

    =IF(SUM(COUNTIF({Sheet1 Range 2}, Output@row) + COUNTIF({Sheet2 Range 2}, Output@row)) > 0, "Dup", "No Match")

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Amanda Carta

    Glad you worked it out. How ever just an FYI. When you have the sum function you dont need the +

    =IF(SUM(COUNTIF({Sheet1 Range 2}, Output@row), COUNTIF({Sheet2 Range 2}, Output@row)) > 0, "Dup", "No Match")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.