Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

EXCEL - Functions, Hlookup, Vlookup, Match & Offset

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
4. Blank
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.
  • 2
1 Solution
See http://www.cpearson.com/excel/lookups.htm#LeftLookup

Eventual alternative: Data|Filter|Autofilter, details see help.
Stellar site for this kind of stuff: http://www.cpearson.com/excel/topic.htm

See for example 'Lookup with multiple criteria'

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now