Double Lookup in grid

[DOUGMORGAN]

A B C D E F G
1 -4 10 10% 20% 30% 40% 50%
2 -3 20 100 200 300 400 500
3 -2 30 200 300 400 500 600
4 -1 40 300 400 500 600 700
5 0 50 400 500 600 700 800
6
7 -3 30%
If anyone could help i would apprieciate it.
The formula i require is VLOOKUP(B7,A1:B5,2,FALSE) I then need to lookup D7
looking for a value in the grid C2:G5 that corresponds with the row in the
first lookup,in this example 300.I then need to add this to the first lookup
value of 20.
The answer should be 20 + 300 in this example.

Doug Morgan

 

[Jason Morin]

The 2nd formula would be:

=INDEX(C1:G5,MATCH(B7,A1:A5,0),MATCH(D7,C1:G1,0))

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi Doug

try the following
VLOOKUP(B7,A1:B5,2,FALSE) +
OFFSET($A$1,MATCH(B7,A1:B5,0)-1,MATCH(D7,C1:G1,0)+1)

for this have a look at
http://www.cpearson.com/excel/lookups.htm
(search for 'Double Lookups')

HTH
Frank

=OFFSET($F$8,MATCH(F16,$F$9:$F$13,0),MATCH(G16,$G$8:$K$8,0))