Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Double entry lookup
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? -- Al Eaton |
#2
|
|||
|
|||
On Mon, 13 Dec 2004 02:12:03 GMT, "Al Eaton" wrote:
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? 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),CONCAT ENATE(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 |
#3
|
|||
|
|||
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! In article , "Al Eaton" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need check two worksheets to lookup a value | Excel Discussion (Misc queries) | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Double Lookups | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |