2 dimensional array lookup with TEXT chars in body of array. How do using array formulas?

[ChetShannon]

Hello,
I am trying to use a 2 dimensional array with a lookup value on the X-axis and one on the Y-axis. I know how to do this when there are numeric values in the body of the array. I would do it like this {=SUM(IF(G4:G6=G9,IF(H3:K3=G10,H4:K6)))} where G9 is the Y axis target lookup and G10 is the X axis target lookup, G4:G6 is Y axis header, H3:K3 is X axis header. This array formula will return the value at the intersection of G9 and G10's values. (Works fine when numeric values are in H4:K6.)

But how would one make this work when the body of the range (H4:K6) is non-numeric values (text for example) because the SUM won't work on the text values. Thanks! (I know there are other ways to do the 2 dimensional lookup using INDEX and MATCH but am looking for a simpler way using Array formulas.) Other methods referenced here - http://www.techonthenet.com/excel/formulas/2d_lookup3.php.

Chet

 

[sheetspread]

I know there are other ways to do the 2 dimensional lookup using INDEX and MATCH but am looking for a simpler way using Array formulas.

why do you think it's simpler?

 

[danzon]

Index/Match is the easiest solution here it seems

=INDEX($G$3:$K$6,MATCH(G9,$G$3:$G$6,0),MATCH(G10,$G$3:$K$3,0))

 

[ChetShannon]

Thank you so much! I was kind of hoping for an array based solution. (No special reason really. I just like the array solutions.)..
Chet