Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup on 2 (and 3!) dimensions
I have a table with three dimensions (day, name, size).
Row 1 has "size" headings (e.g. "10x10", "20x20", "30x30"). And Columns A & B look like this: Sun Abby Sun Bob Sun Chris Mon Abby Mon Bob Mon Chris Tue Abby Tue Bob etc... Is there a formula that will give me the data for, say, "Monday" & "Bob" & column F? I would usually use VLOOKUP with MATCH, but having another dimension in column B is throwing me for a loop. What about a formula for all three ... e.g. "Tuesday" & "Abby" & "30x30"? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup on 2 (and 3!) dimensions
=INDEX(C2:C20,MATCH(1,(A2:A20="Tue")*(B2:B20="Bob" ),0))
and =INDEX(A2:N20,MATCH(1,(A2:A20="Tue")*(B2:B20="Bob" ),0),MATCH("20x20",1:1,0)) which are array formulae, it should be committed with Ctrl-Shift-Enter, not just Enter. Adjust ranges to suit. -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message ups.com... I have a table with three dimensions (day, name, size). Row 1 has "size" headings (e.g. "10x10", "20x20", "30x30"). And Columns A & B look like this: Sun Abby Sun Bob Sun Chris Mon Abby Mon Bob Mon Chris Tue Abby Tue Bob etc... Is there a formula that will give me the data for, say, "Monday" & "Bob" & column F? I would usually use VLOOKUP with MATCH, but having another dimension in column B is throwing me for a loop. What about a formula for all three ... e.g. "Tuesday" & "Abby" & "30x30"? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup on 2 (and 3!) dimensions
=INDEX(C2:E9,SUMPRODUCT((A2:A9="Tue")*(B2:B9="Abby ")*ROW(A2:A9))-ROW(A1),MAT
CH("30x30",C1:E1,0)) =A4*1.14*VLOOKUP(A4,{0,0.75;100,0.78;200,0.81},2,1 ) See example: http://cjoint.com/?elsfU1EGcm HTH -- AP a écrit dans le message de ups.com... I have a table with three dimensions (day, name, size). Row 1 has "size" headings (e.g. "10x10", "20x20", "30x30"). And Columns A & B look like this: Sun Abby Sun Bob Sun Chris Mon Abby Mon Bob Mon Chris Tue Abby Tue Bob etc... Is there a formula that will give me the data for, say, "Monday" & "Bob" & column F? I would usually use VLOOKUP with MATCH, but having another dimension in column B is throwing me for a loop. What about a formula for all three ... e.g. "Tuesday" & "Abby" & "30x30"? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup problem | Excel Worksheet Functions | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |