| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup and return value 12 rows below
Okey great
/HA14 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| 2 Dimensional Lookup by column & rows to return score grade | Excel Discussion (Misc queries) | |||
| How do I return entire rows in a lookup of one sheet to another? | Excel Worksheet Functions | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
| Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Hybrid Mode
