Can the Match formula work for two different column types?

Mark Miloslavic

I'm trying to match two different column types from two different sheets. The first column, Employee Name, is a Contact List type and the second column from another sheet, Full Name, is a Text/Number type with a concatenated first and last name formula. Can the Match formula find a pattern match between two different column types? I'm not sure if I'm doing this right, I'm receiving a #REF in the column with the Match formula.

=MATCH([Employee Name]@row, {Scientific Applications Administration Range 2}, 0)

=[First Name]@row + " " + [Last Name]@row

Genevieve P.

Hi @Mark Miloslavic

Generally MATCH doesn't like separate column types, no. However I believe it looks at the display name for the contact in this instance, so you may be able to find a match between a contact column and a text/number column.

#REF indicates that the cross sheet reference does not exist. Can you check what you have {in these} to ensure it's looking at the right column?

Additionally, can you clarify what it is you're looking to do with the MATCH function? MATCH will return the row number that the matching value is found on in {your range}. If you're just looking to see if that name exists, I would suggest using a COUNTIF Function instead, and wrap that in an IF statement:

=IF(COUNTIF({Scientific Applications Administration Range 2}, [Employee Name]@row) > 0, "Exists", "Does not exist")

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Mark Miloslavic

Generally MATCH doesn't like separate column types, no. However I believe it looks at the display name for the contact in this instance, so you may be able to find a match between a contact column and a text/number column.

#REF indicates that the cross sheet reference does not exist. Can you check what you have {in these} to ensure it's looking at the right column?

Additionally, can you clarify what it is you're looking to do with the MATCH function? MATCH will return the row number that the matching value is found on in {your range}. If you're just looking to see if that name exists, I would suggest using a COUNTIF Function instead, and wrap that in an IF statement:

=IF(COUNTIF({Scientific Applications Administration Range 2}, [Employee Name]@row) > 0, "Exists", "Does not exist")

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Mark Miloslavic

Hi Genevieve,

Thank you so much for your response to my post!

I tested the Match formula using it in different ways and you are correct, the matches only work if the column types are the same. As a work around to my problem, I created a Contact List column on the Scientific Application Administration sheet, filled out the new column with the appropriate data, and was able to get the row index for all the matches, which will allow me to collect data from specific rows for the other sheet.

Thank you again Genevieve. 😁

Genevieve P.

No problem! Thanks for sharing your solution 🙂