Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - possible vlookup/hlookup combination with IF or oth
HI,
I'm trying to work out a formula that will bring back data from a table as below 1 2 3 4 5 60 20 10 6 40 5 61 1 25 70 45 12 62 7 70 20 52 10 I need a formula that would compare the x & y axis of the table when given the values and report back the associated cross value. I was thinking along the lines of a vlookup and h lookup in combination with an IF statement somehow. Or is there a specific formula within excel? Many thanks -- Adam&ellie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - possible vlookup/hlookup combination with IF oroth
With your example table in cells A1:F4, and using A10 for the row
values (60 to 62) and B10 for the column values (1 to 5), put this formula in C10: =INDEX(B2:F4,MATCH(A10,A2:A4,0),MATCH(B10,B1:F1,0) ) Just change the values in A10 and/or B10 to get the appropriate value in C10. Hope this helps. Pete On Jun 13, 10:14*am, adam&ellie wrote: HI, I'm trying to work out a formula that will bring back data from a table as below * * * * * * *1 * * *2 * * *3 * * *4 * * *5 60 * * * * 20 * *10 * * 6 * * *40 * *5 61 * * * * 1 * * *25 * * 70 * *45 * *12 62 * * * * 7 * * *70 * * 20 * *52 * *10 I need a formula that would compare the x & y axis of the table when given the values and report back the associated cross value. I was thinking along the lines of a vlookup and h lookup in combination with an IF statement somehow. Or is there a specific formula within excel? Many thanks -- Adam&ellie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - possible vlookup/hlookup combination with IF oroth
presuming yr data is in A1:F4
put e.g. 61 in A8 and 3 in B8 try: =OFFSET($A$1;MATCH(A8,$A$2:$A$4,),MATCH(B8,$B$1:$F $1,)) the result should be 70 HIH |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help - possible vlookup/hlookup combination with IF or oth
Hi
=INDEX($B$2:$F$3,MATCH(X1,$A$2:$A$3,0),MATCH(Y1,$B $1:$F$1,0)) , where X1 and Y1 contain search values for row- and column headers. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "adam&ellie" wrote in message ... HI, I'm trying to work out a formula that will bring back data from a table as below 1 2 3 4 5 60 20 10 6 40 5 61 1 25 70 45 12 62 7 70 20 52 10 I need a formula that would compare the x & y axis of the table when given the values and report back the associated cross value. I was thinking along the lines of a vlookup and h lookup in combination with an IF statement somehow. Or is there a specific formula within excel? Many thanks -- Adam&ellie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and hlookup in the same formula | Excel Discussion (Misc queries) | |||
Hlookup, Vlookup and IF in combination?? | Excel Discussion (Misc queries) | |||
Combination of Vlookup & Hlookup | Excel Discussion (Misc queries) | |||
can hlookup and vlookup be used in combination ? | Excel Discussion (Misc queries) | |||
SUMIF - HLOOKUP Combination | Excel Worksheet Functions |