Forum Discussion

AmberRose's avatar
AmberRose
Copper Contributor
Apr 20, 2020

Vlookup with Two variables

I am trying to figure out a way to lookup a value when two different variables match and can't figure out how to do it.  For the attached example I want to pull back the explanation on the December t...
excel
Formulas and Functions
AmberRose's avatar
AmberRose
Copper Contributor
Apr 20, 2020

Zack Barresse Thanks so much for the reply Zack!  I might have posted this to the wrong group if this is just for Office 365, sorry, I'm new to this.   I have Office 2016.  You mentioned an array function, I am not familiar. Do you know what the formula would be in Office 2016?

Thanks!

Zack Barresse's avatar
Zack Barresse
Iron Contributor
Apr 20, 2020

AmberRose the easiest way is probably to create a helper column. This is just a calculated column adjacent to your data. In your file, that would be in the 'Dec' sheet in column E. We use this to create what we call a foreign key - something unique made up of other parts.

 

The formula in 'Dec' E2 would be:

=A2&B2

 

Then in your 'March' sheet, your formula in D2 would be a simple INDEX/MATCH:

=IFERROR(INDEX(Dec!D:D,MATCH(A2&B2,Dec!E:E,0)),"")

 

You would want your ranges identically-sized. If you were using a Table you could reference the column via structured references. This doesn't take into account any duplicate keys.

 

While you could use an array formula, it's easier with a helper column.

 

HTH

Resources