VLOOKUP Multiple Matches

VLOOKUP Multiple Matches

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?


Best Answer


  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

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

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

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

    =JOIN(COLLECT({Referenced Sheet - Example},[email protected],{Referenced Sheet - Latest Value},1))

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    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.

  • edited 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 NewcomePaul Newcome ✭✭✭✭✭

    @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.

  • edited 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.

  • edited 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,[email protected])),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?

  • edited 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.......

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

  • edited 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?

  • edited 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 NewcomePaul Newcome ✭✭✭✭✭

    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([email protected] = MAX(COLLECT(Created:Created, Example:Example, [email protected])),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)

  • 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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    COLLECT is basically like adding an IF to any function.

    =COLLECT(range_to_collect, criteria_range_1, criteria_1, criteria_range_2, criteria_2, ............................)

  • Paul, I understand the IF function very well, but the COLLECT seems different. Everyone who defines it uses the word collect in their definition, or repeats the syntax. Is there a plain English description of this function, like "looks at the within the stated range, compares them to the criteria listed, and returns the largest value...", or something like this?

Sign In or Register to comment.