EXCEL - Functions, Hlookup, Vlookup, Match & Offset
Posted on 2003-02-27
I have an excel spreadsheet.
Column A Row 1 - Label (PName/PManager)
Column A Row 2 - Project Name
Column A Row 3 - Project Manager
Column B Row 1 - Label (Project Number)
Column B Row 2 - Project Number
Column C Row 1 - Label (Month)
Column C Row 2 - Cost
Column C Row 3 - C, B, P, etc (C=Construction, B=Bid, P=Program, etc)
Before I start, I have attempted to move Column B, Row 2 down to equal row 3 but to no luck. There are over 2,000 rows so I cannot easily move data around.
What I am attempting to do is to extract the Project Number by what Phase (C, B, P, etc)
I have tried Vlookup, Hlookup, Match, Offset, and I cannot determine which function to use. Please help!
Below is an example of above mentioned setup.
A B C D
1. PName/PManager P# Dec Jan
2. John Doe Building 111 $10000 $15000
3. J. Smith B C
5. Jane Doe Building 222 $7500 $9000
6. J. Smith P C
And the column goes on for several months and there are over 2000 rows.
Above is an example of one project and a possible situation is to extract the Project # from all projects with B in Dec.
The result I am looking for is Project # 111.
I tried V Lookup but it only returns data in the same row and to the right plus the project number is a row above what I want to extract (C, B, P, etc). I tried to nest an Offset function within Vlookup but to no luck.
I know there is a way to do it, just can't seem to get the functions to work correctly.