If/Index/Match help!!

JohnM_BV

Hi! I am relatively new to Smartsheet. Usually I can poke along and work through to get the result. Today, not so much. I am trying to pull data based on data input. With the data, I need to match and select from one of 3 columns on another sheet. I have attached a couple screenshots. User selects accumulation from the list.

After the amount is selected, I want the value based on the selected snow accumulation and crossed to the store, to populate.

The source is from the price list. Based on location and snowfall, the cost varies. The formula needs to recognize the data the user selected for accumulation, then identify which column to pull the data base on the user's entry.

I can use Index/Match to source a dollar amount based on a specific column and store match. I can't figure out how to recognize that data entered by the user and return a value based on the entry and that matches the store.

=INDEX({Snow Tracker_2-4 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0))

I have been on this for hours...

Happy New Year!!

Kelly Moore

Hello @JohnM_BV

One approach would be to construct a nested IF statement and you point the INDEX/MATCH, such as you built above to the appropriate source column range.

=IF([Snowfall Accumulation Projected]@row="2-4 Plow & Shovel", INDEX({Snow Tracker_2-4 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="4-8 Plow & Shovel", INDEX({Snow Tracker_4-8 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="8-12 Plow & Shovel", INDEX({Snow Tracker_8-12 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="12+ Plow & Shovel", INDEX({Snow Tracker_12+ Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0))))))

Another approach would take a modification to your dropdown list so that a number proceeds the response. The number corresponds to the position of the columns in relationship to each other. #1 is your 2-4" column.

The range you select for the INDEX is all of your inches columns ; beginning with your 2-4" column and continuing through to your Salt Lot and Sandlot to make one table. Your store column is not included in this range. Once you select the range as a Table, you are able to leverage the optional term with the INDEX function - the column number. Because you have your source data structured as a table with contiguous columns, you can use this.

=INDEX({Snow Tracker 2"-Sandlot columns}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0), VALUE(LEFT([Snowfall Accumulation Projected]@row)))

Would this work for you?

Kelly

Kelly Moore

Hello @JohnM_BV

One approach would be to construct a nested IF statement and you point the INDEX/MATCH, such as you built above to the appropriate source column range.

=IF([Snowfall Accumulation Projected]@row="2-4 Plow & Shovel", INDEX({Snow Tracker_2-4 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="4-8 Plow & Shovel", INDEX({Snow Tracker_4-8 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="8-12 Plow & Shovel", INDEX({Snow Tracker_8-12 Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0)), IF([Snowfall Accumulation Projected]@row="12+ Plow & Shovel", INDEX({Snow Tracker_12+ Plow & Shovel}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0))))))

Another approach would take a modification to your dropdown list so that a number proceeds the response. The number corresponds to the position of the columns in relationship to each other. #1 is your 2-4" column.

The range you select for the INDEX is all of your inches columns ; beginning with your 2-4" column and continuing through to your Salt Lot and Sandlot to make one table. Your store column is not included in this range. Once you select the range as a Table, you are able to leverage the optional term with the INDEX function - the column number. Because you have your source data structured as a table with contiguous columns, you can use this.

=INDEX({Snow Tracker 2"-Sandlot columns}, MATCH([Store #]@row, {Snow Tracker_Store #}, 0), VALUE(LEFT([Snowfall Accumulation Projected]@row)))

Would this work for you?

Kelly

JohnM_BV

Hi Kelly!

Thank you for the response! Both examples worked perfectly. Originally, I was trying to do a version of both examples with If/Index/Match and a range. I like the "cleaner" formula approach on the 2nd example.

Much appreciated!!

~John