Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in the row below the target row
I have the following table.
part start mid end ABCD plan 01-Jan 01-Feb 01-Mar actual/projected 01-Jan 05-Feb 05-Mar EFGH plan 01-Feb 01-Mar 01-Apr actual/projected 10-Feb 10-Mar 10-Apr With this vlookup formula, I can lookup the plan date - =VLOOKUP("ABCD plan",A2:D5,2,FALSE) but I really want to capture the actual/projected date in the row below the plan row. How do I do this? -- Alan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in the row below the target row
Try this:
=INDEX(B2:B5,MATCH("ABCD plan",A2:A5,0)+1) To get the other dates change B2:B5 to C2:C5 or D2:D5. Hope this helps. Pete On May 10, 1:14*am, AG wrote: I have the following table. part * * * * * * * * * * * * * * * * * *start * mid * * end ABCD plan * * * * * * * * * * * * * * * * * * * 01-Jan *01-Feb *01-Mar * * * * *actual/projected * * * 01-Jan *05-Feb *05-Mar EFGH plan * * * * * * * * * * * * * * * * * * * 01-Feb *01-Mar *01-Apr * * * * *actual/projected * * * 10-Feb *10-Mar *10-Apr With this vlookup formula, I can lookup the plan date - * * *=VLOOKUP("ABCD plan",A2:D5,2,FALSE) but I really want to capture the actual/projected date in the row below the plan row. How do I do this? -- Alan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup in the row below the target row
This worked.
I have another question: Is there a way to capture the text color as well? Alan -- Alan "Pete_UK" wrote: Try this: =INDEX(B2:B5,MATCH("ABCD plan",A2:A5,0)+1) To get the other dates change B2:B5 to C2:C5 or D2:D5. Hope this helps. Pete On May 10, 1:14 am, AG wrote: I have the following table. part start mid end ABCD plan 01-Jan 01-Feb 01-Mar actual/projected 01-Jan 05-Feb 05-Mar EFGH plan 01-Feb 01-Mar 01-Apr actual/projected 10-Feb 10-Mar 10-Apr With this vlookup formula, I can lookup the plan date - =VLOOKUP("ABCD plan",A2:D5,2,FALSE) but I really want to capture the actual/projected date in the row below the plan row. How do I do this? -- Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
Use Vlookup or similiar function to find the row of the target cel | Excel Discussion (Misc queries) | |||
Variable VLOOKUP source and target | Excel Worksheet Functions | |||
Target | Excel Worksheet Functions | |||
Can VLOOKUP deliver the second occurence when target column entri. | Excel Worksheet Functions |