Multiple Lookups

[Kokomojo]

My workbook is set up as follows:

Magazine,Year,FullPage,HalfPage,QuarterPage
People,2009,300,200,100
Time,2009,150,100,50
Us,2009,200,100,0
People,2008,250,225,200
Time,2008,100,50,0
Us,2008,50,25,0

I would like to set up a lookup table to and write a function to tell me,
for example, what the FullPage number is People in 2008.

Thanks!

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"


=INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10="People")*($B$2:$B$10=2008),0))

If this post helps click Yes

 

[Luke M]

Similar to Jacob's solution, but non-array:

=INDEX(C:C,SUMPRODUCT(--(A2:A10="People"),--(B2:B10=2008),ROW(A2:A10)))

Note that SUMPRODUCT can't callout entire column (e.g. A:A) unless using XL
2007.

 

[T. Valko]

Try this...

With your table in the range A1:E7

Lookup values:

G1 = People
H1 = 2008
I1 = FullPage

=SUMPRODUCT(--(A2:A7=G1),--(B2:B7=H1),INDEX(C2:E7,,MATCH(I1,C1:E1,0)))

 

[Teethless mama]

=SUMPRODUCT(--(Magazine="People"),--(Year=2008),FullPage)

 

[Shane Devenshire]

Hi,

Try

=SUMPRODUCT((A2:A7=G1)*(B2:B7=G2)*(C1:E1=G3)*C2:E7)

Where your data runs from A1:E7 with titles on row 1 and the Magazine, Year
and AddSize in G1, G2, and G3.