array formula with if and hlookup

[Guest]

trying to make an array formula to look in row 2 for "4" and return from row
3, if row 1 = "1" looking for the answer "d"
row1: 1 1 2 1 1
row2: 7 5 4 4 6
row3: A B C D E
trying {=IF(A1:E5=1,HLOOKUP(4,A2:E5,2,FALSE)," ")}
hoping someone smarter than me can help
bport jim

 

[sitaram.pothukuchi]

Shouldn't the ranges be A1:E1 etc.?

try:

=INDEX(A3:E3,MATCH(B5&B6,A1:E1&A2:E2,0))

entered using Ctrl+Shift+Enter

 

[Guest]

One way, array-entered with CTRL+SHIFT+ENTER:
=INDEX(A3:E3,MATCH(1,(A2:E2=4)*(A1:E1=1),0))

You could point to cells instead for the criteria: 4 and 1
within the ranges: A2:E2 and A1:E1

 

[Guest]

my mistake on the 2nd range in formula should be A2:E3

 

[Guest]

thanks to both "it's a good day when you learn something"

 

[Max]

Glad you got it working.

.. "it's a good day when you learn something"

Yup, that happens everyday around here <g>. Every post / response(s)
would carry that learning benefit not just for the original poster, but
for all newsgroup readers as well ..

---