Multiple Lookups
 

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!

Multiple Lookups
 

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
---------------
Jacob Skaria


"Kokomojo" wrote:

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!

Multiple Lookups
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kokomojo" wrote:

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!

Multiple Lookups
 

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)))

--
Biff
Microsoft Excel MVP


"Kokomojo" wrote in message
...
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!

Multiple Lookups
 

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


"Kokomojo" wrote:

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!

Multiple Lookups
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kokomojo" wrote:

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!