headermask image

Business Tools Blog

HLOOKUP & VLOOKUP

You have data and you want to use it.  Crazy, huh?

The HLOOKUP and VLOOKUP functions, when used together are your answer.

Start with your data.

  • The columns are months
  • Rows are customers
  • The Data is Revenue.
  • I numbered the columns 1 thru 8 in row 4
  • I numbered the rows 1 thru 11 in column H

Photobucket

Next - define what you are trying to lookup. I want to see Customer 3’s Revenue in May

  • First, I will use the Vlookup function to determine what row Company 3’s data can be found
  • FORMULA = VLOOKUP(A2, A5:H15,8,FALSE)
  • A2=”Company 3” - this is what we are looking up
  • A5:H15= the data table (outlined in red)
  • 8 = the number of columns that you need to go over to return the row’s value
  • FALSE = will return a “false” value if Company 3 does not exist in the data table

The answer is 4 … Company 3 is in row 4 of the data table

Photobucket

Next - write your HLOOKUP formula.

=HLOOKUP(B2, A5:G15, C2, FALSE)

  • B2 = MAY, the month you want to look up
  • A5:H15= the data table (outlined in red)
  • C2 = the number of rows that you need to go down to return the Revenue = 4
  • FALSE = will return a “false” value if May does not exist in the data table

Photobucket

That seems like a lot of work … but, now that the formulas are written … you can change the Customer or the Month and the Revenue will automatically appear.

  • I changed the customer to Company 9
  • I change the month to FEB
  • The Revenue that appears is 137.0

Photobucket

If you liked my post, feel free to subscribe to my rss feeds

Money Making Options

Post meta

This entry was written by Sandi Mays and posted on December 15, 2008 at 12:45 am and filed under Excel. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment(Latest is displayed first) or leave a trackback: Trackback URL.

Share with others

Post HLOOKUP & VLOOKUP to digg. Post HLOOKUP & VLOOKUP to Reddit add to stumbleupon Google Bookmark myweb bookmark on del.cio.us newsvine Tailrank magnolia Furl co.mments shadows simpy blinklist

6 Comments so far (Add 1 more)

  1. Hi Shawnna, If I were trying to do this, I would use a pivot table and filter on blanks

    1. Sandi Mays on August 25th, 2011 at 12:00 pm

  2. I am trying to figure out how many items I need to order for every item listed. I have a sheet of items (rows) where for each row there may be a quantity needed or not. For each row that has a quantity needed, I need to produce another sheet with ONLY the rows where there is an actual need with the quantity needed, item name and state. Here is sample data of the inventory in Worksheet A:

    Name State Qty
    Apples WA 5
    Oranges FL
    Car MI 8
    Furniture VA 3
    Corn IN

    Notice that the Qty is null in many of the rows. Desired output on Worksheet B:

    Name State Qty
    Apples WA 5
    Car MI 8
    Furniture VA 3

    So the output does not include the empty quantity records. I’ve tried various row formulas and others and am just having a hard time. I’d prefer to do this with a formula rather than VBA macro or such. Any assistance would be gratifying.

    2. Shawnna Kuster on August 25th, 2011 at 10:54 am

  3. Brent, Thanks for the note! Now you need to start using HLOOKUP too :)

    3. Sandi Mays on June 5th, 2008 at 8:24 am

  4. I learned all I know about Vlookups from Sandi! And it has continued to serve me well.
    -b

    4. Brent Fontana on June 5th, 2008 at 8:21 am

  5. Thank Joe :) I’m so happy that you found this helpful!

    5. Sandi Mays on June 4th, 2008 at 8:32 am

  6. As I find myself relearning these functions a couple times a year, I’m going to now bookmark this post and cascade to my team. I find they are asking similar questions from time to time. Cheers.

    6. Joe on June 4th, 2008 at 8:30 am

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*