Vlookup with Two variables

[AmberRose]

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 tab onto the March tab for each line Item if the ledger account and the Fund matches.  Ledger accounts can be in several Funds and Funds have numerous ledger accounts.  So they both need to match before bringing back the explanation.  I have tried Vlookups and 'If' statements but can't find anything that works. 

Any help would be greatly appreciated.  Thanks!

[#2]

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

[#3]

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

[#4]

@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

[#5]

@AmberRose you can use array formula for that. Best if you convert your data into table. Please see attached file. It's not VLOOKUP but maybe it does the job for you.

{=INDEX(results_range;MATCH(criterion1&criterion2;criterion1_lookup_range&criterion2_lookup_range;0))}