LookUp/Match

[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 trying to find a formula for row 2 in the table below that results like
this:

Date AA BAC DELL
20071217 0.76% NotFound 0.80%


Thank You in Advance

 

[Mike H]

Carl,

Possibly one of my increasingly frequent elderly moments but I can't
understand your question.

I trying to find a formula for row 2 in the table below that results like
this:
Date AA BAC DELL
20071217 0.76% NotFound 0.80%

the data returned bears no resembelance to anything in row 2 but is close to
that in row 4 but seems to have an extra field BAC that is somewhat
confusing. Why not post your formula however wrong you may think it is.

Mike

 

[Dave F]

OK, lemme guess. GOOG's beta is higher than either AA's or DELL's, so
my guess is your data is the day over day percentage price change in
these three company's respective stocks.

But where does BAC fit into this?

In any event, you can achieve what (I think) you want by using a
combination of the OFFSET, INDEX, and MATCH functions.

Dave

 

[carl]

Thanks Mike. I have done a poor job trying to explain my problem.

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 then want to create a new table that will look at my data table, match
date and headers in B11 and return the value in the data table. For
example, if my new table looked like this.

Date AA BAC DELL
20071217 0.76% NotFound 0.80%

The formula in B22 looks at the Data Table, finds match on Date and Match
On header then returns the appropriate value. If a match can't be found,
formula returns "NotFound".

Does this clarify ?

 

[carl]

Thanks. Can you provide an example ?

Thanks Mike. I have done a poor job trying to explain my problem.

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 then want to create a new table that will look at my data table, match
date and headers in B11 and return the value in the data table. For
example, if my new table looked like this.

Date AA BAC DELL
20071217 0.76% NotFound 0.80%

The formula in B22 looks at the Data Table, finds match on Date and Match
On header then returns the appropriate value. If a match can't be found,
formula returns "NotFound".

Does this clarify ?

 

[Teethless mama]

In B2: =IF(COUNTIF(Header,B$1),SUMPRODUCT(--(Date=$A2),INDIRECT(B$1)),"Not
Found")

copy across and down