VLOOKUP with Duplicate Data

Kevin Segrist

I have a master sheet that may have duplicated column data, like:

Client Name | Status (dropdown) | Delivery Date | Feature (checkbox)

Client A | Rescheduled | 2/5/2020 | 0

Client B | Postponed | 2/12/2020 | 1

Client A | Confirmed | 3/17/2020 | 1

Client C | Requested | | 0

I have a sheet I'm using as a template to create additional sheets that looks like this:

ClientName | Delivery Date | Feature

<Link to Master> | VLOOKUP (col 2) | VLOOKUP (col 3)

The user makes a copy of the template sheet, creates a link to Client Name in the master sheet, and the remaining data is auto-populated using that value, like this:

Delivery Date ==> =VLOOKUP(ClientName1, {Master Sheet Range}, 2, false)

Feature ==> =VLOOKUP(ClientName1, {Master Sheet Range}, 3, false)

I don't own the master, so modification (other than very minor ones) to suit my needs is impossible.

This approach has several inherent problems, three of which I'm addressing with my post:

1) When a project is in "Postponed" or "Rescheduled" status, a new entry for the client MAY or MAY NOT be added to the master sheet. I don't encounter issues until a new entry IS added. When a new entry is added, I am forced to manually update the ClientName link to the new entry.

2) Of course, after updating my ClientName link, VLOOKUP still returns data from the first entry. I have been given edit rights, so to circumvent this issue I make SMALL modifications to any previous occurrances of "Client Name" in the master sheet, like removing the dot from St. Mary's, to make St Mary's imediately solves that issue... it's pretty clunky, and I don't want users to have to do this.

3) A third problem, I hadn't previously been using column headers to select my range, so frequently I discovered #NO MATCH errors and had to redefine the range. I solved that when I learned I could use the column headers, but found that if I select only the first column, I get #INCORRECT ARGUMENT SET errors, which is resolved by just selecting all the columns... Am I misunderstanding how to properly select a range for my VLOOKUP functions?

It's difficult to be consise, I hope I've clearly illustrated my problems. I'm new to Smartsheet and its formulas, so I'm looking for a relatively simple solution that addresses at least the first two issues above.

Thank you, as always!

Kevin Segrist

Sorry about the formatting... next time I'll use screenshots.

Kevin Segrist

Kevin Segrist

I think I just had a rare Eureka moment... This works.

=JOIN(COLLECT({MasterSheetRange Column 3}, {MasterSheetRangeColumn 2}, <>"Postponed", {MasterSheetRangeColumn 2}, <>"Rescheduled", {MasterSheetRange Column1}, VLOOKUP(TaskName1, {MasterSheetRange Column1}, 1, false)))

Returns: "Project Manager 2

=JOIN(COLLECT({MasterSheetRange Column 7}, {MasterSheetRangeColumn 2}, <>"Postponed", {MasterSheetRangeColumn 2}, <>"Rescheduled", {MasterSheetRange Column1}, VLOOKUP(TaskName1, {MasterSheetRange Column1}, 1, false)))

Returns: "8/11/20"

Etc...

I hope this is helpful to anyone having similar struggles!

Andrée Starå

@Kevin Segrist

Excellent!

Glad you got it working!

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!