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

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

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