EXCEL - Functions, Hlookup, Vlookup, Match & Offset

Posted on 2003-02-27
Medium Priority
Last Modified: 2012-05-04
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.
Question by:mrbucc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 13

Accepted Solution

cri earned 150 total points
ID: 8037078
See http://www.cpearson.com/excel/lookups.htm#LeftLookup

Eventual alternative: Data|Filter|Autofilter, details see help.
LVL 13

Expert Comment

ID: 8037096
Stellar site for this kind of stuff: http://www.cpearson.com/excel/topic.htm

See for example 'Lookup with multiple criteria'

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
New style of hardware planning for Microsoft Exchange server.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question