Can I use VLOOKUP to find exceptions in a file and MATCH on those exceptions?

Rick Girard

I have 2 tables:

1. Source table - Data the is pulled in weekly (automated export)
2. Destination Table - Takes Data from Source - using INDEX / MATCH data to find common ID NUMBERS between the tables.

To get the common ID NUMBERS to match, once the weekly export is added to the SOURCE file, I search and find the new ID NUMBERS in the Source table, then manually add the new ID NUMBER's to the Destination table.

This triggers the INDEX / MATCH formulas in all the columns of the Destination table to bring in the other data associated with that ID NUMBER from the Source table. (multiple columns get pulled in)

Instead of manually doing this , can I use a VLOOKUP reference to MATCH on exceptions?

In practice this would first look at ID NUMBERS in the DESTINATION Table and for those that ARE NOT A MATCH in the SOURCE TABLE, bring those new (unmatched ) ID numbers into my Destination sheet. Then as i update the Source sheet with new weekly data the VLOOKUP will find the new ID NUMBERS without me manually adding them to the Destination sheet.

Hope that made sense. Any ideas how to construct a formula using VLOOKUP or another formula method?

thanks

RickG

Genevieve P.

Hi @Rick Girard

Based on the scenario you described, I would recommend the premium add-on called DataMesh. This will not only be able to bring in the new values, but it can replace your current INDEX(MATCH formulas to map data across sheets using a DataMesh workflow as well.

Here's more information on DataMesh: DataMesh | Smartsheet and Smartsheet DataMesh: Eliminate Typos, Avoid Duplicative Data Entry, and More

If you don't have access to DataMesh, my next suggestion would be to use a Report to pull in your columns. This way new rows with new IDs will automatically appear in the Report with their associated details.

If a Report won't work for you, then I would continue to manually copy/paste over your ID Numbers from one sheet to the next to trigger your formulas. There may be a way to use a formula to populate values (eg. using a Row Number as the matching value instead of the ID), but it believe it may get a bit messy or you may run into circular reference errors in the formulas.

Cheers,

Genevieve