# COUNTIFS + IFERROR + MATCH?

Options
✭✭
edited 04/21/23

Good afternoon,

I have two sheets I am working with, referenced here as Sheet A and Sheet B. Both sheets contain an identical column, which I will call "ID Number".

Sheet A contains another column, which I will call "ID Location". On Sheet A, there may be multiple rows with the same "ID Number", as they can be in multiple "ID Locations". So, for instance, ID Number 123 may be in ID Location A on row 1, and also listed as existing in ID Location B on row 5.

I would like a formula, if possible, in Sheet B, that checks Sheet A for a matching ID Number, and then returns the following:

If one match - return text in Sheet B column "ID Location".

If two matches - return text 'Both'.

If no match - return text 'Error'.

Hence why I believe there will be a COUNT function of some kind involved, along with an IFERROR and a MATCH function.

Is this possible?

• ✭✭✭✭✭✭
Options

You would need a series of IF/COUNTIFS combos and an INDEX/MATCH.

=IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 1, INDEX({Location}, MATCH([ID Number]@row, {Number}, 0)), IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 0, "Error", "Both"))

• ✭✭✭✭✭✭
Options

You would need a series of IF/COUNTIFS combos and an INDEX/MATCH.

=IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 1, INDEX({Location}, MATCH([ID Number]@row, {Number}, 0)), IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 0, "Error", "Both"))

• ✭✭
Options

That worked! Thank you so much for your help!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!