COMBINE FORMULA TO LOOK UP 2 DIFF SHEETS

Bobby1

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

Mike TV

@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

i get incorrect argument

Genevieve P.

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 🙂

Bobby1

Genevieve P.

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

Bobby1

=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.

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