Lookup and return value 12 rows below
 

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

Lookup and return value 12 rows below
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"HA14" wrote in message
ups.com...
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

Lookup and return value 12 rows below
 

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)


In article . com,
HA14 wrote:

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

Lookup and return value 12 rows below
 

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

From
HA 14

Lookup and return value 12 rows below
 

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.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"HA14" wrote in message
ups.com...
Thank you very much both - just great. What is the difference between
using the match type "0" or "false"?

From
HA 14

Lookup and return value 12 rows below
 

Okey great

/HA14