Multiple Lookups

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

I'm trying to do a multiple lookup - my table looks like the following


Date Type 12 24 36
12/1/2008 1 .10 .12 .14
12/1/2008 2 .15 .18 .19
1/1/2009 1 .11 .13 .15
1/1/2009 2 .20 .23 .24


I need to lookup based on information in three cells
Date 12/05/08
Type
 
Jack said:
I'm trying to do a multiple lookup - my table looks like the following


Date Type 12 24 36
12/1/2008 1 .10 .12 .14
12/1/2008 2 .15 .18 .19
1/1/2009 1 .11 .13 .15
1/1/2009 2 .20 .23 .24


I need to lookup based on information in three cells
Date 12/05/08 - select 12/1 vs 1/1 row
Type 2
Click to expand...
months 24 select the column

result would be .18
 
As long as the dates are sorted in ascending order as is shown in your
sample...

Your table in the range A1:E5

Lookup values:

A10 = date = 12/5/2008
B10 = type = 2
C10 = period = 24

Entered as an array** :

=INDEX(C2:E5,MATCH(1,(LOOKUP(A10,A2:A5)=A2:A5)*(B2:B5=B10),0),MATCH(C10,C1:E1,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top