INDEX MATCH MULTIPLE CRITERIA

Bobby1

I need an index match formula that can return a match when multiple criteria are met. such as All A's & B's in sheet 2 and indicating a MATCH in sheet 1

Genevieve P.

Hi @Bobby1

If you simply want to know if a row in a second sheet has the same values as a row in your current sheet, I would actually suggest using a COUNTIFS Function:

=COUNTIFS({Column 1 other sheet}, [Column 1]@row, {Column 2 other sheet}, [Column 2]@row, {Column 3 other sheet}, [Column 3]@row, {Column 4 other sheet}, [Column 4]@row)

This will return a number if any rows in the other sheet match your current row. If you want it to say "MATCH" instead of a number, you can add an IF statement around it:

=IF(COUNTIFS({Column 1 other sheet}, [Column 1]@row, {Column 2 other sheet}, [Column 2]@row, {Column 3 other sheet}, [Column 3]@row, {Column 4 other sheet}, [Column 4]@row) > 1, "MATCH", "")

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

Cheers,

Genevieve

Bobby1

Hello,

Below I have pasted the formula & screenshots of slightly different variations and the outcome.

=IF(COUNTIFS({TEST Copy of Invoicing Process Tracker 202 Range 1}, Payee@row, {{TEST Copy of Invoicing Process Tracker 202 Range 4}, [Agrmt No. 1]@row, {{TEST Copy of Invoicing Process Tracker 202 Range 5}, [Total Inv. Amount]@row, {TEST Copy of Invoicing Process Tracker 202 Range 6}, [Invoice No.]@row, {TEST Copy of Invoicing Process Tracker 202 Range 2}, [Invoice Date]@row) > 1, "MATCH", ""))

=IF(COUNTIFS({TEST Copy of Invoicing Process Tracker 202 Range 1}, Payee@row, {{TEST Copy of Invoicing Process Tracker 202 Range 4}, [Agrmt No. 1]@row, {{TEST Copy of Invoicing Process Tracker 202 Range 5}, [Total Inv. Amount]@row, {TEST Copy of Invoicing Process Tracker 202 Range 6}, [Invoice No.]@row, {TEST Copy of Invoicing Process Tracker 202 Range 2}, [Invoice Date]@row) > 1, "MATCH", "")

=IF(COUNTIFS({TEST Copy of Invoicing Process Tracker 202 Range 1}, Payee@row, {{TEST Copy of Invoicing Process Tracker 202 Range 4}, [Agrmt No. 1]@row, {{TEST Copy of Invoicing Process Tracker 202 Range 5}, [Total Inv. Amount]@row, {TEST Copy of Invoicing Process Tracker 202 Range 6}, [Invoice No.]@row, {TEST Copy of Invoicing Process Tracker 202 Range 2}, [Invoice Date]@row > 1, "MATCH", ""))