COUNTIFS + IFERROR + MATCH?

Jerexify

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?

Thank you in advance.

Paul Newcome

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

Paul Newcome

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

Jerexify

That worked! Thank you so much for your help!

Paul Newcome

Happy to help. 👍️