Trying to find a match on 2 values between sheets then pull one or more values from the 2nd sheet

David Hundley

I have a master sheet with a two keys (eg, Customer and SKU). I am trying to find a match on these 2 values in a lookup table and pull in Price to the master sheet. I have tried a number of functions using VLOOKUP, INDEX, MATCH in combination but cannot get it right. Below are the 2 sheets and function I am trying to use:

Here's how I setup the cross-sheet references:

The current formula seems to pick up correct values in some cases (eg, rows 4,6,7), but not all (#invalid value). Anyone got any ideas what I am doing wrong or have other suggestions?

David Joyeuse

Hi @David Hundley

The problem in your formula is that the INDEX function is structured like this:

=INDEX([Value to return], [Row Number], [Column Number]).

So your second MATCH while being written correctly is treated as a Column number in the INDEX formula. Hence the errors.

The only way I found to do a MATCH based on two columns, is to have an helper Column on both Sheet (that you can hide in both). Maybe there's another way and I'd love to read about it, but that's all I got so far:

Concatenate all the rows you want in this column with something like:

=[CustomerID]@row + [SKU]@row.

Then do your INDEX/MATCH on both these helper columns.

Hope it helped!

David Joyeuse

Hi @David Hundley

The problem in your formula is that the INDEX function is structured like this:

=INDEX([Value to return], [Row Number], [Column Number]).

So your second MATCH while being written correctly is treated as a Column number in the INDEX formula. Hence the errors.

The only way I found to do a MATCH based on two columns, is to have an helper Column on both Sheet (that you can hide in both). Maybe there's another way and I'd love to read about it, but that's all I got so far:

Concatenate all the rows you want in this column with something like:

=[CustomerID]@row + [SKU]@row.

Then do your INDEX/MATCH on both these helper columns.

Hope it helped!

David Hundley

@David Joyeuse This concatonation approach worked just as you suggested. I was trying to accomplish the task without creating a helper column - and got caught up in the challenge. But I was concerned that I was going to fail given that I did not find any other direct references to this in previous community Q&A nor in the examples in the Smartsheet documentation. I will go with your approach and move on. Go with what works...right? Thanks for your advice!

David Joyeuse

@David Hundley

You're welcome David!

One note though, you have to make sure that the concatenation of two always return unique values, otherwise, your INDEX/MATCH function will not work properly.

Still, I was quite tired when answered as a JOIN/COLLECT would have been as efficient:

=VALUE(JOIN(COLLECT({Fetch Source Price}, {Fetch Source CustomerID}, [CustomerID]@row, {Fetch Source SKU}, [SKU]@row))))