Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp and Match Question
My Data Table is Like this:
Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I am trying to find a formula for B2:D2 below that will produce this result.. Date GOOG DELL IBM 20071217 4.37% 0.80% NotFound Thank YOu in Advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp and Match Question
Ok, let's say your data goes from A1:D6, and 20071214 is in G1 and Goog is in
H1, then this function viww find the value that corresponds to 20071214 and Goog: =IF(ISNA(MATCH(H1,A1:D1,0)),INDEX(A1:D6,MATCH(G1,A 1:A6,0),MATCH(H1,A1:D1,1)+1),INDEX(A1:D6,MATCH(G1, A1:A6,0),MATCH(H1,A1:D1,0))) The matched value is: 0.0225 If you want an alternative, this function will do the same thing: =INDEX(B2:D6,MATCH(G1,A2:A6),MATCH(H1,B1:D1)) Merry Christmas, here's one mo =INDEX($A$1:$D$6,MATCH(G1,$A$1:$A$6,0),MATCH(H1,$A $1:$D$1,0)) Regards, Ryan--- -- RyGuy "carl" wrote: My Data Table is Like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I am trying to find a formula for B2:D2 below that will produce this result.. Date GOOG DELL IBM 20071217 4.37% 0.80% NotFound Thank YOu in Advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp and Match Question
Say that your output headers are in K1:N1 (K1 contains "Date"). Thus
K2 will contain 20071217. In L2: =VLOOKUP(K2,$A$1:$D$6,MATCH(L$1,$A$1:$D$1,0),0) HTH Kostis Vezerides On Dec 21, 7:56 pm, carl wrote: My Data Table is Like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I am trying to find a formula for B2:D2 below that will produce this result.. Date GOOG DELL IBM 20071217 4.37% 0.80% NotFound Thank YOu in Advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LookUp/Match/INDEXing Question | Excel Worksheet Functions | |||
LookUp/Match Question | Excel Worksheet Functions | |||
Match Lookup question | Excel Worksheet Functions | |||
A question for Match and Lookup | Excel Discussion (Misc queries) | |||
LookUp/Match Question | Excel Worksheet Functions |