Forum Discussion

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 question.xlsx9 KB

Formulas and Functions

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