VLookup with multiple references in one cell

ThePinkBird

Hey all,

I'm not even sure what I'm after is possible, but thought I'd ask anyway.

We're using a sheet to track users while they are out on test rides. Each model available for test rides has a number assigned to it. When users take a ride, we enter their name and the test ride ID# to identify who is on which (TRACKER). I have a separate sheet with the models, their ID# and a few other details listed (REFERENCE).

I'd like to be able to extract data from the REFERENCE sheet to the TRACKER sheet based on the ID#. This is simple enough using vlookup. The trouble I encounter is that many of our users are in groups. Because our test rides are so fast paced, we don't really have the option of multiple columns, multiple user entries, etc. So what we tend to do is enter all the ID#s into the same cell, separated by commas. This obviously mucks with the vlookup formula and gives a #NO MATCH result. I'm hoping that the commas are enough to be able to adjust the formula and still get the references.

Any help is greatly appreciated!

Andrée Starå

Hi @ThePinkBird

I hope you're well and safe!

You could add multiple so-called helper columns where you'd use a formula that would look at the different IDs.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Naeem Ejaz

@ThePinkBird I can help please share the details

ThePinkBird

Hi Naeem,

Thank you! What exactly do you need?

Due to confidentiality at work, I can't share the sheets, but I did make copies:

This is the Reference sheet: https://app.smartsheet.com/sheets/xPpwcq8vJ4V2mPc4h9MmFF95PvH9GC83WVxqVfr1

This is the Tracker sheet: https://app.smartsheet.com/sheets/954W5QMFqq2Hm5VPPW2JQx8RjV87GCxmFwMGfWV1

Andrée Starå

Hi @ThePinkBird

I hope you're well and safe!

You could add multiple so-called helper columns where you'd use a formula that would look at the different IDs.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@ThePinkBird

For clarification.

We would break out the cell with the IDs to different cells in different columns on the same row and do the same VLOOKUP or INDEX/MATCH on those.

Make sense?

ThePinkBird

@Andrée Starå

Thank you for the tip! I think I've got it down at this point. I am running into a small problem though. I'm successfully parsing out the data when separated by a comma. What I'm trying to do now is use that data in a vlookup formula. It doesn't seem to recognize the output.

So one column has the following formula:

=LEFT([ID#]@row, [Helper POS1]@row - 1)

This is successfully giving the accurate output. I want to take the output and put it in the following formula:

=VLOOKUP([Bike ID#1]@row, {SD Test Fleet Service Tracker Range 1}, 2)

Bike ID#1 is the column with the =LEFT formula in it. When this formula runs, it gives me a #NO MATCH, but if I clear the =LEFT formula and just put in the number, the Vlookup formula runs successfully. This was never really a problem in Excel so I'm not sure of what workaround there might be to make it work in Smartsheet.

Thank you for any tips!