Match multiple cells and lookup related value

**excelforum123** · 07-19-2010, 10:33 PM

Hi,
I have tried using VLookup, Index, Match, Dget, and other commands and still cannot figure out what to do. I listed an example problem below. I need a formula that will automatically fill in values under H, such as when E,F,&G = 1,3,&7, 8.2 is output to H. If someone already has a solution to this, please just reference me to that page. Hopefully this makes some sense. Your help would be GREATLY appreciated!

A B C D
1 3 5 8
1 3 6 8.1
1 3 7 8.2
1 4 5 8.3
1 4 6 8.4
1 4 7 8.5
2 3 5 8.6
2 3 6 8.7
2 3 7 8.8
2 4 5 8.9
2 4 6 9
2 4 7 9.1

E F G H
1 3 7
2 4 6

**Paul** · 07-19-2010, 11:36 PM

Hi Excelforum,

Without using any helper columns, assuming your original values are in A1:D12, and your lookup values are in E1:G1, E2:G2, etc. then in H1 insert the array formula:

=INDEX(D1:D12,MATCH(E1&F1&G1,A1:A12&B1:B12&C1:C12,0))

This needs to be confirmed with CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically add braces around your formula. Fill that formula down as many rows as needed.

**excelforum123** · 07-19-2010, 11:48 PM

Thanks so much Paul! I guess the important part was using control,shift,enter. Works great!

**CFF** · 05-18-2012, 06:49 AM

The match-array method works very well, but only if the lookup array is in the same file. When the numbers to lookup are located in another (open) I used the SUMIFS function.
Using same assumptions as previous poster (your original values are in A1:D12, and your lookup values are in E1:G1, E2:G2, etc.), and further assuming there is only one row in the D-column which match the description (otherwise the value returned will be too high, as it is a sum of all matching rows)

Then in H1 insert the following formula (no array):

=SUMIFS($D$1:$D$12,$A:$1:$A$12,E1,$B$1:$B$12,F1,$C$1:$C$12,G1)

This works also if any of the references are in other workbooks.

/Christina