COMBINE FORMULA TO LOOK UP 2 DIFF SHEETS

Options

I need formula that will look at 2 sheets with different criteria but return field ops when a match is made.

Sheet A might not have a match but sheet B does... and vice versa

or

Both sheets has a match and I need the " field ops to return in 1 column



=IF(COUNTIF({Field Ops Invoice Tracker Invoice}, [Invoice No.]@row) > 1, "Field Ops")

+

=IF(COUNTIF({Field Ops Contract Tracker contract #}, [Agrmt No. 1]@row) > 1, "Field Ops")



Tags:

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Bobby1

    =IF(COUNTIFS({Field Ops Invoice Tracker Invoice}, [Invoice No.]@row, {Field Ops Contract Tracker contract #}, [Agrmt No. 1]@row)>0, "Field Ops", "")

    This might work, depending on how you've got things set up. I also changed your formula from >1 to >0 because that seems like the logic you're looking for.

  • Bobby1
    Bobby1 ✭✭✭✭
    Options

    i get incorrect argument

  • Genevieve P.
    Options

    Hi @Bobby1

    Can you post a screen capture showing your formula open in the sheet, with Mike's suggestion? It looks like it should work, so I'm wondering if there's a difference between what's in your cell and what's written here. It may be a case of a parentheses missing 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Genevieve P.
    Options

    Hi @Bobby1

    Thanks for clarifying, this helps a lot! I misunderstood how your two sheets were set up.

    In this instance, you won't be able to reference separate sheets within the same function - your original structure is what I would suggest, using two separate COUNTIFs within an IF statement.

    Example structure:

    =IF(

    OR(COUNTIF(Sheet A) > 1, COUNTIF(Sheet B) > 1),

    "True", "False")


    So in your case, try this:

    =IF(OR(COUNTIF({Field Ops Invoice Tracker Invoice}, [Invoice No.]@row) > 1, COUNTIF({Field Ops Contract Tracker contract #}, [Agrmt No. 1]@row) > 1), "Field Ops", "")


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Bobby1
    Bobby1 ✭✭✭✭
    Options


    =IF(OR(COUNTIF({Field Ops Invoice Tracker INVOICE #}, [Invoice No.]@row) > 1, COUNTIF({Field Ops Contract Tracker Range 1}, [Agrmt No. 1]@row) > 1), "Field Ops", "")


    I tried the formula and it worked but it is returning " field ops" when the "agrmnt .1" is blank. instead of matching exactly

  • Genevieve P.
    Options

    Hi @Bobby1

    It's finding multiple rows in your source sheet that have blank cells, which is why you have "Field Ops" returned. I would suggest adding another IF statement at the beginning to simply return Blank if the current Agrmt No. is blank, like so:

    =IF([Agrmt No. 1]@row = "", "", IF(OR(COUNTIF({Field Ops Invoice Tracker INVOICE #}, [Invoice No.]@row) > 1, COUNTIF({Field Ops Contract Tracker Range 1}, [Agrmt No. 1]@row) > 1), "Field Ops", ""))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!