Index, match, vlookup with multiple criteria

**saniamarco** · 08-28-2012, 11:08 PM

I have been working on this for too long and I can't find the answer anywhere. If someone could PLEASE HELP ME, I would appreciate it.

I have two worksheets, "sourceuse" and "current". Current is the raw data that I am trying to use to populate the sourceuse worksheet.

in cell d8 of sourceuse, I'd like the output to be e4 of current. The criteria I'm matching is "org, account, and operating" So, basically if on the number in column b of sourceuse, matches column c on current AND d6 of sourceuse (total expenditures) matches column d of current AND d3 of sourceuse matches e1 of current.

I have tried vlookup, index and match and I can't think anymore, nothing is working for me. UGH!

**vlady** · 08-28-2012, 11:44 PM

hello
in D8
=INDEX(CURRENT!$E$2:$E$130,MATCH(B8,IF(CURRENT!$D$2:$D$130=$D$6,CURRENT!$C$2:$C$130),0))
note array formula - ctrl+shift + enter not just enter

you'll find the formula enclosed with {formula } then fill down

**saniamarco** · 08-28-2012, 11:52 PM

Interesting. I am getting circular reference error?? Any thoughts on that?

**vlady** · 08-29-2012, 12:53 AM

check the ranges
CURRENT!$E$2:$E$130
the bold parts.

Copy of DUMMY.xlsx

**saniamarco** · 08-29-2012, 05:10 PM

I actually found the error which was in the value of the Total Expenditures field. Thank you so much for your help. There is only one problem. The output value is E2 instead of E4. I think it is because it is stopping at the first instance of the "org". Is there a way around this?

**vlady** · 08-29-2012, 07:16 PM

You did not pres ctrl+alt+enter -instead of just enter - it's an array formula.
once you press ctrl+alt+enter you'll notice the formula will be contained between two braces {=.....}