Multiple Lookups

  • Thread starter Thread starter Kokomojo
  • Start date Start date
K

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!
 
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
 
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.
 
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)))
 
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.
 
Back
Top