Double entry lookup

A

Al Eaton

I have the following table in Excel:

Ticker Field 1998 1999 2000 2001
A Price 65.76 37.08
A Rev 7952 8331 10773 8396
A OP INC 919 1216 1548 -44
A OP MRG 11.56 14.60 14.37
AA Price 17.89 28.71 31.88 36.95
AA Rev 15340 16323 22936 22859
AA OP INC 2652 2821 4304 3523
AA OP MRG 17.28 17.28 18.76 15.41
AAPL Price 12.99 22.33 52.10 19.80
AAPL Rev 5941 6134 7983 5363
AAPL OP INC 379 471 704 -231
AAPL OP MRG 6.38 7.68 8.82
ABC Price 29.23 30.46 22.02 52.64
ABC Rev 8575 9760 11610 15823
ABC OP INC 166 191 218 281
ABC OP MRG 1.94 1.96 1.87 1.77



I want to look up the 2000, OP MRG for the Ticker AA. So you lookup the
ticker, then the field you want and finally the year of the data.

If cell C20 = Ticker and cell C21 = Field; then
The formula below will find the correct value for the 1998 column
only.

{=INDEX$C$2:$C$17,MATCH$C$20&$C$21,$A$2:$A$17&$B$2:$B$17,0))}


How do I add the Year lookup to the mix?
 
R

Ron Rosenfeld

I have the following table in Excel:

Ticker Field 1998 1999 2000 2001
A Price 65.76 37.08
A Rev 7952 8331 10773 8396
A OP INC 919 1216 1548 -44
A OP MRG 11.56 14.60 14.37
AA Price 17.89 28.71 31.88 36.95
AA Rev 15340 16323 22936 22859
AA OP INC 2652 2821 4304 3523
AA OP MRG 17.28 17.28 18.76 15.41
AAPL Price 12.99 22.33 52.10 19.80
AAPL Rev 5941 6134 7983 5363
AAPL OP INC 379 471 704 -231
AAPL OP MRG 6.38 7.68 8.82
ABC Price 29.23 30.46 22.02 52.64
ABC Rev 8575 9760 11610 15823
ABC OP INC 166 191 218 281
ABC OP MRG 1.94 1.96 1.87 1.77



I want to look up the 2000, OP MRG for the Ticker AA. So you lookup the
ticker, then the field you want and finally the year of the data.

If cell C20 = Ticker and cell C21 = Field; then
The formula below will find the correct value for the 1998 column
only.

{=INDEX$C$2:$C$17,MATCH$C$20&$C$21,$A$2:$A$17&$B$2:$B$17,0))}


How do I add the Year lookup to the mix?
Click to expand...



Put the year in C22.

Name your data table TBL
Name your first two columns Ticker and Field

Try the following **array** formula:

=HLOOKUP(C22,TBL,MATCH(CONCATENATE(C20,C21),CONCATENATE(Ticker,Field),0)+1,FALSE)

To enter an array formula, after typing or pasting the formula into the formula
bar, hold down <ctrl><shift> while hitting <enter>. XL will place braces {...}
around the formula.






--ron
 
D

Domenic

Here's another way...

=INDEX(C2:F17,MATCH(1,(A2:A17="AA")*(B2:B17="OP
MRG"),0),MATCH(2000,C1:F1,0))

OR

=INDEX(C2:F17,MATCH(1,(A2:A17=A21)*(B2:B17=B21),0),MATCH(C21,C1:F1,0))

....where A21 contains the Ticker, B21 contains the Field, and C21
contains the Year.

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Top