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


  • 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 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))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!