Creating Lookup field for users to find route options...

Matt Tancock

Afternoon !

I'm trying to create a Look up field where users can type in a starting location and an ending location and would be presented with results from our available routes. I assume I would need to use index and match but don't ton of expertise in this regard.

In other threads i see folks have suggested building out the source sheet which i have done and have attached. We have about 300 more rows to include but this example will give you the framework/idea.

But then i think i read you would need to build another supplementary sheet that has the "search field" with an embedded formula to query the source data when typed in...

need help...

Thank you :-)

Runs Transport example 2023.xlsx

Lucas Rayala

Hi @Matt Tancock, you would want to use an INDEX COLLECT combo instead of INDEX MATCH. Collect allows you to provide multiple criteria for the lookup. In the sheet that users are entering their start and end info, instead of letting them free type, create a [Start Location] and [End Location] column with the start and end selection criteria in a drop down. Make sure those titles match the start and end criteria in the lookup table you created.

Your formula would then be:

INDEX(COLLECT({Target Info Column}, {Start Location}, [Start Location]@row, {End Location}, [End Location]@row), 1)

That Target Info Column is whatever column you want to pull from your lookup sheet. The "1" at the end of the formula just tells the Index to provide the first match; however, you probably only have one match anyway.

Matt Tancock

https://community.smartsheet.com/discussion/comment/401062#Comment_401062

Thank you so much for the help !

I think in our case we actually have multiple routes that will be presented (different size vehicles etc) so would I need to change much to display all potential matches?

Lucas Rayala

@Matt Tancock, sorry about the late reply. You could use the JOIN function to get multiple options, but they wouldn't be able to select between them, it would just display them. That's:

=JOIN(COLLECT({Target Info Column}, {Start Location}, [Start Location]@row, {End Location}, [End Location]@row), CHAR(10))

In this case, the JOIN will display all matching entries supplied by the COLLECT function. The second part of the JOIN function is the "delimiter", or what you want to go between the multiple entries. For instance, if you put "," then it would put a comma between entries. I happen to like "CHAR(10)" as a delimeter -- the CHAR function is a special character function, and special character "10" is the return. If you use this, and make sure your column is set to "wrap" the text, then all the entries will appear on their own line (within the same cell).