Trying to find a match on 2 values between sheets then pull one or more values from the 2nd sheet
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?
Best Answer
-
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!
Answers
-
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 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!
-
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))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!