Business Tools Blog

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

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

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

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