array formula with if and hlookup
 

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

array formula with if and hlookup
 

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

array formula with if and hlookup
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bport jim" wrote:
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

array formula with if and hlookup
 

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

"bport jim" wrote:

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

array formula with if and hlookup
 

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

"bport jim" wrote:

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

array formula with if and hlookup
 

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
bport jim wrote:
thanks to both "it's a good day when you learn something"