Help with "INDEX MATCH"

Hello, I'm kind of new to Smartsheet and I've been tasked with transitioning a lot of data into Smartsheets, with that said -

I have a data sheet that I need to INDEX MATCH actuals into a forecast sheet. Whenever I try to enter an INDEX MATCH formula, it keeps saying that multiple criteria is not supported or the MATCH option is not available. I have even tried the INDEX COLLECT version with no luck. Any help is appreciated.

For example - 555 Apartments had one actual expense in March (from my "data (3) - TEST"), and it was for GL 1450-00400…

I need to match that amount to that property for that GL code for March into my forecast sheet.

This is the current formula I have (obviously wrong as I keep getting the #UNPARSEABLE error) - I have over 1,000 rows so it would be tedious to do this manually every month.

=INDEX(COLLECT({{data (3) - TEST Range 5}}, {{data (3) - TEST Range 2}}, "[Property]@row", {{data (3) - TEST Range 3}}, "[Property]@row", {{data (3) - TEST Range 4}}, "[January]"), 1)

Please help!

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @AP8331

First, I recommend using the "sheet name & column name" convention like {data (3) - TEST : actuals}, to make your formula easy to understand, later. (See the image below.)

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

Then, there are a couple of points I want to fix.

Your formula

=INDEX(COLLECT({{data (3) - TEST Range 5}}, {{data (3) - TEST Range 2}}, "[Property]@row", {{data (3) - TEST Range 3}}, "[Property]@row", {{data (3) - TEST Range 4}}, "[January]"), 1)

You do not have to quote like "[Property]@row".

The second condition should be [GL Combined]@row, supposing {data (3) - TEST Range 3} refers to the GL column.

Lastly, "[January]" needs to be simply "January" or "March", in my example.

I added IFERROR( , "No Match"), in case there is no matching date, to avoid errors.

=IFERROR(INDEX(COLLECT(, {data (3) - TEST : Sub Property Name}, Property@row, {data (3) - TEST : GL}, [GL Combined]@row, {data (3) - TEST : Month - Month}, "March"), 1), "No Match")

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

AP8331

That worked! Thank you so much! 😊

All comments

jmyzk_cloudsmart_jp

Hi @AP8331

First, I recommend using the "sheet name & column name" convention like {data (3) - TEST : actuals}, to make your formula easy to understand, later. (See the image below.)

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

Then, there are a couple of points I want to fix.

Your formula

=INDEX(COLLECT({{data (3) - TEST Range 5}}, {{data (3) - TEST Range 2}}, "[Property]@row", {{data (3) - TEST Range 3}}, "[Property]@row", {{data (3) - TEST Range 4}}, "[January]"), 1)

You do not have to quote like "[Property]@row".

The second condition should be [GL Combined]@row, supposing {data (3) - TEST Range 3} refers to the GL column.

Lastly, "[January]" needs to be simply "January" or "March", in my example.

I added IFERROR( , "No Match"), in case there is no matching date, to avoid errors.

=IFERROR(INDEX(COLLECT(, {data (3) - TEST : Sub Property Name}, Property@row, {data (3) - TEST : GL}, [GL Combined]@row, {data (3) - TEST : Month - Month}, "March"), 1), "No Match")

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

AP8331

That worked! Thank you so much! 😊

jmyzk_cloudsmart_jp

Happy to help!😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.