Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is this possible with excel ? | Excel Discussion (Misc queries) | |||
Is there any way to use HLOOKUP in an array formula to return multiple matches? | Excel Worksheet Functions | |||
HLookup? or an array function?? | Excel Worksheet Functions | |||
How do I find a value in an array (VLOOKUP? HLOOKUP?) | New Users to Excel | |||
formula using both vlookup & hlookup | Excel Worksheet Functions |