Lookup and return value 12 rows below

H

HA14

I am trying to find a formula to lookup in a table and return the
value to the right 12 rows below. In the example below my search value
is 02-2007 and I would like to return the value 36.000 which is 12
rows below next to 02-2008.

search value 02-2007

01-2007 10.000
02-2007 12.000
03-2007 14.000
04-2007 16.000
05-2007 18.000
06-2007 20.000
07-2007 22.000
08-2007 24.000
09-2007 26.000
10-2007 28.000
11-2007 30.000
12-2007 32.000
01-2008 34.000
02-2008 36.000

result 36.000

Can someone help me?

Thanks
HA14
 
B

Bernard Liengme

If the data is in A1:B14
And D1 hold the lookup value (02-2007)
then use =INDEX(B1:B14,MATCH(D1,A1:A14,0)+12)

MATCH finds the position of the lookup value within the A range
INDEX returns an item from an Array based on its position
best wishes
 
J

JE McGimpsey

One way (assuming your table's in columns A&B, and you lookup value is
in D1):

=INDEX(B:B,MATCH(D1,A:A,FALSE)+12)
 
H

HA14

Thank you very much both - just great. What is the difference between
using the match type "0" or "false"?

From
HA 14
 
B

Bernard Liengme

There is no difference. The Help on MATCH uses 0 and 1 but since these are
the same as TRUE and FALSE when dealing with Boolean variables either can be
used.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top