VLOOKUP Multiple Matches

Dennis Wierzbicki · 05/21/20

I have a table that sometimes has duplicate entries in a column. I'm doing a VLOOKUP on this column, however, obviously, if there is more than one match, this generates an error.

What I'd like to do is return a match for the most recent duplicate entry, or in the case of a sheet that is filled via a form, the item farther down the sheet than all other duplicates.

Any suggestions how this could be accomplished?

Thanks

Paul Newcome · 05/27/20

It pulls together ALL values from the first range based on the range/criteria sets.

When you nest it inside of another function, that function will only evaluate the data that was pulled by the COLLECT function.

Think of it almost as a JOIN function but on the back-end and with the ability to add criteria, and then the main function evaluates the "text string".

Paul Newcome · 05/21/20

Do you have a system generated column in place to track when the form entries are added?

Dennis Wierzbicki · 05/21/20

Paul, no, but I was headed that way, to allow me to do a time calculation. What are you thinking?

ToddF · 05/21/20

Instead of VLOOKUP, which matches on only one column, you need to use a different function.

Add a system column for Row ID.
Add a column for Latest Value (checkbox).
In the Latest Value column, use a formula =IF([Row ID]@row=MAX(COLLECT([Row ID]:[Row ID],Example:Example,Example@row)),1,0)

When you do your lookup, you need to evaluate two columns, Example and Latest Value.

=JOIN(COLLECT({Referenced Sheet - Example},Example@row,{Referenced Sheet - Latest Value},1))

Dennis Wierzbicki · 05/21/20

Todd, thanks! Let me run off and see if I can apply this to what I'm doing, and see if this takes care of my challenge.

Paul Newcome · 05/21/20

My solution was going to end up being similar to the one above, so I will go ahead and wait to see what you come back with.

Dennis Wierzbicki · 05/22/20

Todd, I presume your Example column is a system column "Created", or is this the column that contains the duplicate entries? I'm getting confused a bit, as I'm not familiar with the COLLECT function.

Paul Newcome · 05/22/20

@Dennis Wierzbicki I believe an Auto-number column is being used. For that I personally use a text/number type of column and a formula to replicate the row number.

Either way... You would reference the MAX number in this column which would be the entry that is the furthest down the sheet.

Dennis Wierzbicki · 05/22/20

Guys, this is what I'm getting. I'm getting the check mark column being checked TWICE. Can't see how this would result in a discrete value being returned.

Dennis Wierzbicki · 05/22/20

Paul, thanks. I'm still struggling to understand the Example column in what Todd offered. For the COLLECT function, Smartsheet not so cleverly says it's used to "collect" values, which is a classic case of using a word to define itself.

I see how the formula =IF([Row ID]@row=MAX(COLLECT([Row ID]:[Row ID],Example:Example,Example@row)),1,0) looks for the MAX value in the Row ID column, but then it "Collects" the corresponding value in the Example column in the same row, compares the two values somehow.....that's where I go off into the weeds. The value in the Example column could be anything, so how is this value compared to a number in the Row ID column to determine if the checkbox is a 1 or a 0?

Dennis Wierzbicki · 05/22/20

Ok, this seemed to work...although I can't see how, for the life of me. Off to the lookup function. Uh oh, Todd used the COLLECT function again. For some reason I canNOT understand how it works.......

Dennis Wierzbicki · 05/22/20

I've used JOIN before, but how is it use to do a LOOKUP?

Dennis Wierzbicki · 05/22/20

So, I've got my data sheet working properly. I'm doing a lookup of the value "1" in the Example column, when the Latest Value is checked, and want to return the text "Target Column 12".

I'm assuming the JOIN function mentioned above joins the values in the Example column, and the Latest Value column when the Latest Value column is "1", then I use this result to VLOOKUP on the 4th column, entitled Target Column?

Dennis Wierzbicki · 05/22/20

I give up. This is like trying to enter a conversation of PhD's speaking a language you are barely conversant in.

How does the JOIN function combine with the COLLECT function to provide a lookup capability? Or, do I perform a LOOKUP on the result of the JOIN/COLLECT combo?

I hate feeling stupid like this, especially after working with Smartsheet for over 10 years.

Paul Newcome · 05/22/20

I took another look, and you actually don't need a row ID column. Just look at the max date for the particular example.

=IF(Created@row = MAX(COLLECT(Created:Created, Example:Example, Example@row)),1,0)

Then to pull the data you would use something along the lines of

=INDEX(COLLECT({Other Sheet Column To Pull From}, {Other Sheet Example Column}, [Target Sheet Example]@row, {Other Sheet Checkbox Column}, 1), 1)

Dennis Wierzbicki · 05/22/20

Paul, that did the trick! Thanks! Now I just have to figure out what's happening inside those COLLECT cells so I can use this apparently valuable function in the future.

Formulas and Functions

Formulas and Functions

VLOOKUP Multiple Matches

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary