Index/Match Function help for Indexing multiple Columns

brent.kendall

I'm creating essentially a search engine for my organization to quickly pull data from a large data collection. I am running into a wall with the Index/Max function and trying to get Smartsheet to be searching multiple columns to pull the information from a column in the row with the #1 in the row.

Staff will submit a form (1st picture) to answer what service they're looking for. There will always be only one column with information per row (or per submission). I'm wanting Smartsheet to search the row with the number "1" (in the "latest" column) between the highlighted square columns and bring back the information in whatever column has information into the "resource" box (picture 2).

Smartsheet let's me select all columns as a "search range" using the control function but brings back "invalid column value" as the answer. The answer should be "GLO Center" and for the previous submission, "National Prevention Life".

My formula is as follows:

=(INDEX({Community Resource Search Page Range 4}, MATCH(1, {Community Resource Search Page Range 2}), 0))

Thank you!

Paul Newcome

I would suggest a helper column on the source sheet that pulls all of the individual help type columns into a single column. Then the INDEX/MATCH will be much easier to get working.

=JOIN(COLLECT([1st Help Type Column]@row:[Last Helper Type Column]@row, [1st Help Type Column]@row:[Last Help Type Column]@row, @cell <> ""))