Identify corresponding value and return

Marc Meilert

Good morning,

I am not sure how to formulate this desire into a working function.

My process workflow grid contains 6 columns containing a drop-down single select of around 50 choices. They are TEXT selects for descriptions. I want to identify the corresponding SKU from the text descriptions in a second set of 6 columns (repeat formula). I have a corresponding sheet of the same drop-down values with a column next to it that I want the corresponding SKU to return based on the selection done.

So GRID ONE form fill returns a selected text in column 1, formula searches another sheet GRID 2 for the text in Column 1, then returns the value from the same row, next column and inserts back into column 2 on GRID ONE.

Help?

Or just tell me stop thinking about it because it is not possible :)

Alasdair Cockburn

You can use the Index and Match functions to achieve what you want.

INDEX will return a cell from a range based on the row and column values fed into it.

You can use the MATCH function to get the row you want it to pull from.

INDEX( range, MATCH( search_value, search_range, 0),1)

so for your example it would be

INDEX(GRID 2 column 2, MATCH(GRID 1 cell 1, GRID 2 column 1, 0),1)

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Marc Meilert

Thank you!!! That worked exactly as I requested. Very appreciated!!!

Alasdair Cockburn

You can use the Index and Match functions to achieve what you want.

INDEX will return a cell from a range based on the row and column values fed into it.

You can use the MATCH function to get the row you want it to pull from.

INDEX( range, MATCH( search_value, search_range, 0),1)

so for your example it would be

INDEX(GRID 2 column 2, MATCH(GRID 1 cell 1, GRID 2 column 1, 0),1)

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Marc Meilert

Thank you Alasdair.

I had actually tried using the INDEX and MATCH functions without luck, so I was hoping someone would layout the formula for me. My issue was trying to reference the sheets in wrong order from what I see in your post.

I will give it a try and let you know how it went.

Thanks again.

Marc Meilert

Thank you!!! That worked exactly as I requested. Very appreciated!!!

Alasdair Cockburn

Glad I could help 😀