Using SUMIFS with both vertical and horizontal arrays

[sdchris]

Hello,

I am trying to figure out a way to return one value based on two criteria. One criteria is in a vertical array, whereas the other is in a horizontal array. See the tables below for more details.

Table 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[/TR]
[TR]
[TD]10.01[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]10.02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]A4[/TD]
[TD]A5[/TD]
[TD]A6[/TD]
[TD]A7[/TD]
[TD]A8[/TD]
[TD]A9[/TD]
[TD]A10[/TD]
[TD]A11[/TD]
[TD]A12[/TD]
[/TR]
[TR]
[TD]10.01[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]10.02[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Essentially, I would like to write a formula in Table 1 in the cell that says "FORMULA" which returns a value of "5" from Table 2, based on two criteria within Table 1: "A1" and "10.01". FYI, these two arrays are on different sheets within my workbook. Also, ideally I would like to be able to drag this formula down to several more rows (not shown above).

I tried using SUMIFS and integrating INDEX/MATCH within it, but no luck. Not sure if I used index/match correctly, however; I am not very well versed in that function.

Please let me know if I can provide any additional info. Thanks in advance!

 

[Domenic]

Let's assume the following...

Sheet1!A1:O3

[TABLE="width: 960"]
<tbody>[TR]
[TD="class: xl63, width: 64"]text[/TD]
[TD="class: xl63, width: 64"]text[/TD]
[TD="class: xl63, width: 64"]text[/TD]
[TD="class: xl63, width: 64"]A1[/TD]
[TD="class: xl63, width: 64"]A2[/TD]
[TD="class: xl63, width: 64"]A3[/TD]
[TD="class: xl63, width: 64"]A4[/TD]
[TD="class: xl63, width: 64"]A5[/TD]
[TD="class: xl63, width: 64"]A6[/TD]
[TD="class: xl63, width: 64"]A7[/TD]
[TD="class: xl63, width: 64"]A8[/TD]
[TD="class: xl63, width: 64"]A9[/TD]
[TD="class: xl63, width: 64"]A10[/TD]
[TD="class: xl63, width: 64"]A11[/TD]
[TD="class: xl63, width: 64"]A12[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10.01[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]25[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]40[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10.02[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]40[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2!A1:D3

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A1[/TD]
[TD="class: xl65, width: 64"]text[/TD]
[TD="class: xl65, width: 64"]text[/TD]
[TD="class: xl65, width: 64"]text[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10.01[/TD]
[TD="class: xl65"]text[/TD]
[TD="class: xl65"]text[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10.02[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]


Formula:

Sheet2!D2, copied down:

=INDEX('Sheet1'!$D$2:$O$3,MATCH($A2,'Sheet1'!$A$2:$A$3,0),MATCH($A$1,'Sheet1'!$D$1:$O$1,0))

Hope this helps!

 

[Joyner]

Hello, based on your sample data, it appears you are trying to do a two-way lookup. If so you can use something like this, assumes data starts in cell A1:

=INDEX(A1:O3,MATCH(10.01,A1:A3,0),MATCH("A1",A1:O1,0))

Just substitute the actual cell references for "10:01" and "A1" in the formula (use proper cell referencing to lock the "A1", ie, $A$1 if the test of "A1is in Cell A1)

And use the proper Sheet referencing for the other ranges and expand to suite your actual data.

EDIT: Mr Domenic beat me to it, with a better example with the sheet referencing...

 

[sdchris]

Thank you both; this is exactly what I was looking for.