LookUp/Match

  • Thread starter Thread starter carl
  • Start date Start date
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 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
 
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
 
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
 
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 B1:D1 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 B2:D2 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 ?
 
Thanks. Can you provide an example ?

carl said:
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 B1:D1 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 B2:D2 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 ?
 
In B2: =IF(COUNTIF(Header,B$1),SUMPRODUCT(--(Date=$A2),INDIRECT(B$1)),"Not
Found")

copy across and down
 
Back
Top