INDEX MATCH MULTIPLE CRITERIA

Options

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



Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Bobby1 ✭✭✭✭
    Options

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!