LookUp and Match Question

C

carl

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
 
R

ryguy7272

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,A1: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 more:
=INDEX($A$1:$D$6,MATCH(G1,$A$1:$A$6,0),MATCH(H1,$A$1:$D$1,0))


Regards,
Ryan---
 
V

vezerid

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LookUp/Match 5
LookUp/Match/Index/OffSet Question 4

Top