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.

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.

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.