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
Zack Barresse's avatar
Zack Barresse
Iron Contributor
Apr 20, 2020

AmberRose hello there! This gets a little tricky. In the 'old' days, you used to have to utilize an array function, which you would confirm with CTRL+SHIFT+ENTER instead of just ENTER. This would put the curly brackets { } around your formula. They were very powerful. But, if you're using Office 365, you have access to the FILTER function! Your formula in D2 would be this...

 

=FILTER(Dec!$D$2:$D$5,(Dec!$A$2:$A$5=A2)*(Dec!$B$2:$B$5=B2),"")

 

Copy it down. You want the range references similarly sized. 

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