COMBINE FORMULA TO LOOK UP 2 DIFF SHEETS
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")
Answers
-
=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.
-
i get incorrect argument
-
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 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
=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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!