I need help with an Index match or index collect formula using multiple criteria

Sania

In this formula I need to add and additional criteria where MONTH(Date@row) matches in the other reference {1 Trans Date} I'm not sure if I can use MONTH{1 Trans Date} in the same way.

So far this formula is working, but I can't add the criteria if the month are the same and not receive an error.

=INDEX({1 Transactions #}, MATCH(Amount@row, {1 Trans Amount}, 0), IF(CONTAINS(LEFT(Vendor@row, 8), {1 TransDesc}), 1, 0))

jmyzk_cloudsmart_jp

Hi @Sania

Suppose you have data as shown below.

https://app.smartsheet.com/b/publish?EQBCT=c4aea87215d54a9ebe7e32a26baebe24

To get the 1 Transaction # from the above table using the amount, the first eight letters of the vendor, and the month the transactions are made, you can use the INDEX(COLLECT()) formula like this.

=INDEX(COLLECT({1 Trans : Transactions #}, {1 Trans : Amount}, Amount@row, {1 Trans : Desc}, CONTAINS(LEFT(Vendor@row, 8), @cell), {1 Trans : Date}, MONTH(Date@row) = MONTH(@cell)), 1)

To look up values from a range in another sheet with multiple conditions, the COLLECT function gives you all the values that meet the conditions, and you can pick the first one, all of them, etc. (In this case, the formula picked the first one.)

https://app.smartsheet.com/b/publish?EQBCT=44ea0d4b69a94a2cb6f5acbed312a932