VLOOKUP with two data source reference sheets

LeAndre P

Hello,

I am trying to use a VLOOKUP in a situation where the data source is spread across two different reference sheets. Essentially there are two different data source sheets and the parent sheet where the formula is nested which looks for a match within the other two sheets to return. I’ve managed to get the function to work if searching only one sheet or the other however, when I add the second reference sheet to the formula to search both sheets at once, I get an #UNPARSEABLE error. Any help would be greatly appreciated.

 Working Formula:

=VLOOKUP([Claim ID]@row, {Active Data}, 2, 0)

#UNPARSEABLE Formula:

=VLOOKUP([Claim ID]@row, {Active Data}, {Legacy Data}2, 0)

Thank you,

AFlint

The VLOOKUP function does not support 2 ranges. If you look at the syntax here

https://help.smartsheet.com/function/vlookup

You'll notice there is only one parameter for a range.

There are several other considerations as well:

Could the lookup value [Claim ID] be duplicated in both ranges? If not, you might try 2 columns, one where it looks up in Active Data, and then right next to it Legacy Data. You could then hide these columns and use a third column to pull in whichever one finds the value.

Might not be the most elegant solution, but should get the results. Good Luck!

Andrée Starå

Hi @LeAndre P

I hope you're well and safe!

To add to AFlint's excellent advice/answer.

A formula could check the first range for a match first, and if there isn't one, it should continue and look at the next range.

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

LeAndre P

Hello Andrée,

Yes, this would work, and it is exactly what I was trying to achieve when I added the second range to what turned out to be an #UNPARSEABLE Formula. This all came about after I nested the VLOOKUP function into the worksheet and noticed several line items returned #NOMATCH. As I worked to fix the issue, I realized the formula could not find a match because the data for those line items were on another sheet and without searching both sheets I would continue to get #NOMATCH as a result.

 To AFlint’s suggestion, adding more columns wont exactly work in this situation for several reasons.  The first is, I would need to create four more columns not just two as mentioned and secondly, the data would still be in two separate places(ranges) but now on one sheet instead of two sheets and not in the range I am trying to fill.

Here is the situation. I am working in three sheets, sheetA, sheetB, and sheetC, all of which have two columns having the same name, Claim ID and PO Value, which are the two columns the VLOOPUP is searching on sheetB and sheetsC. SheetB has active data where line items are added when a new claim is submitted, whereas sheetC holds legacy data that is needed to understand expenditures from a past time to date. SheestA was create as a container for active and legacy items to review expenditures without having to visit two separate sheets. So, the hope is to have a formula VLOOKUP or otherwise that will search the Claim ID and PO Value range on sheetB and if there is no match, search sheet sheetsC and return the results to sheet sheetA. I hope this helps and makes more sense.

Thank you,