Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Hlookup in Excel










Is there a way to have a cell reference automatically change in a HLookup formula when copying the formula vertically (as it would if it was a VLookup)?


I typically use the "False" statement in any lookup so I get exact matches, but today I came across a problem where I need the closest match. I thought setting the HLOOKUP to "TRUE" would give me closest match, but nope...

Here is my array:

Row1: 0 45 27 1
Row2: 2 3 4 5

My hlookup formula is uses the value "28" as my look-up value, and scans the above array to return the value in row 2. Since I had it set to "TRUE" I expected it to return the value of 4 since in Row1 the number 27 is closest to 28.

Nope, I get 5... in fact it consistently seems to return only the last column.

So is there some formula out there that would return the value of 4 in the above example, i.e. be smart enough to find the number in row1 that was closest to 28?


Hi there

I have a spreadsheet in which I need to perform a reverse HLOOKUP. The spreadsheet is attached for viewing.

I want a forumula in cell L2, which will look into cell K2 and if K2 is not empty, it will then look along the row to find the first cell that is not empty, then return the column name of that cell.

So in row two, if case closed is a yes, i want it to return the column ref that is not empty, ie if 1st meeting is full, and report written and 2nd meeting, but business written is empty, then it should return '2nd meeting'.

Any clues?
Thanks - Brokovich.


hi, I have a question using an Hlookup function. I was wondering if it was possible to find a number that is located in a cell above the "value" in the Hlookup formula?

for example:

1 2 3 4 5 6 7
yes no maybe please haha monster 12 pk
345 456768 76769 1234 12345 3456 345
whhhha 234j 23jhk m34 9834 ok 234

to find the header number associated with "monster" would I use
=hlookup("monster",A1:G4,-2,False)

I know that if I enter a 2 as the index_number it will show me the second value in that column BELOW monster, but I want to find the value ABOVE monster. is there a way to look up values above the it?


I have a sumif formula where the sum_range is a specific column.

The column varies from month to month. I can identify the column by using
the hlookup function.

Can I return the column letter to the sumif function using the hlookup?
Normally the hlookup returns the value and not the cell address.

Is there another function I am not aware of that will accomplish this?

Thanks for your help.



I'm trying to do an Hlookup but it still doesn't return the data that I need.

Please help...

Attached is my sample file and the output that I need.

Thanks a million!


Is it possible to nest a VLOOKUP and an HLOOKUP to find a value in a chart of data by providing the vertical and horizontal values to search for? Using the below chart as an example, I would want to be able to search for B, 3 to return the result "y3".

1 2 3 4 5
A x x2 x3 x4 x5
B y y2 y3 y4 y5
C z z2 z3 z4 z5
D w w2 w3 w4 w5
E v v2 v3 v4 v5


Hello all,

Here's my problem, i'm looking to enter a formula in which the hlookup is based on 2 criteria; "dept" and "month". For example

So I have a worksheet like this...

......................Dept 1..... Dept 1..... Dep 2..... Dep 2
......................Jan..........Feb...........Jan.........Feb
Expense1..........300.........100...........500.........200
Expense2..........355.........564...........789.........865


I want to put a formula in a cell to lookup dept 1/Jan and return the value for expense1 whichi is 300.

Thanks,
A


I'm trying to get a combobox to retrieve a set of values based on it's current value using a pre-defined table. The code currently doesn't do anything, as I've run into the problem that hlookup is apparently not defined! What am I supposed to type in a macro to get it to do this?

The code I'm using is as follows:

Code:

Private Sub ComboBox1_Change()
Dim str As Integer
Dim dex As Integer
Dim con As Integer
Dim intel As Integer
Dim wis As Integer
Dim cha As Integer
ComboBox1.Clear
Dim racloc As String
racloc = Sheets("race").Range("B2")
For x = 2 To Sheets("race").Range("A1")
   ComboBox1.AddItem racloc
   racloc = Sheets("race").Range("B2").Offset(0, x - 1).Value
   Next
With ComboBox1
    str = HLookup(ComboBox1.Value, race, 2, False)
    dex = HLookup(ComboBox1.Value, race, 3, False)
    con = HLookup(ComboBox1.Value, race, 4, False)
    intel = HLookup(ComboBox1.Value, race, 5, False)
    wis = HLookup(ComboBox1.Value, race, 6, False)
    cha = HLookup(ComboBox1.Value, race, 7, False)
End With
End Sub


I just ran it the one time to initialize it, so the values are in fact, there.. it keeps giving me the not defined error thereafter.


Hello community!

I have a lot of hlookup functions that are linking to another sheet.
the problem is when there is nothing entered in the cell, it returns a 0 value instead of just blank.

I need to distinguish between a real 0 and the virtual 0 that is returned when the cell is empty, therefore i can NOT use something like:

Code:

IF(A1=0,"",A1)


help?


Hi guys, trying to use the INDIRECT function for the first time and having a few problems with it, hope you can help!

Currently got the below formula :

=HLOOKUP($C$11,INDIRECT("'&F7&'!'"&A8:U10),3,FALSE)

where I need to lookup the value in C11 from a sheet name referenced in cell F7.

I think it must be a problem with the "s or 's but I THINK I've used all the different combinations and it still isn't working.

Can anyone help point out where I've gone wrong?

Cheers in advance!


I want to do the following:

Look-up a value in the top row of a table, and return the sum of certain rows below that value.

Simplified example:

A B C
Red Blue
1 John 3 1
2 John 4 2
3 Mike 2 5

I want to look-up "Red", and return the sum of all "Johns" in the Red Column.

I try this formula, but it doesn't work:

=HLOOKUP("Red",A1:C3,SUMIF(A1:A3,"John",B1:B3))

Any thoughts? Perhaps HLookup isn't the right function?
=HLOOKUP


I am trying to use a named range for the table in HLOOKUP where the named range appears in another spreadsheet cell. I have tried entering the range in the HLOOKUP formula as the result of the CELL("contents') function but this does not work, any suggestions? The HLOOKUP function does work correctly if I enter manually the named range in the formula. I am wondering if HLOOKUP will only accept a range or range name and not a formula that retums that name from another cell, if this is so does anyone have any suggestions on how to achieve the table lookup a different way when the table range is a variable?

Thanks


I have cells in Row 4 merged in pairs. That is, N4:O4 are merged into 1 cell, P4:Q4 are merged into 1 cell, R4:S4 are merged into 1 cell, etc, and there are 55 such pairs up to DR4:DS4.

Cells in Row 7 are not merged in pairs.

I would like some way of doing a HLOOKUP across the 55 merged cells in Row 4 and return both values in the corresponding cells in Row 7 underneath the matching cell Row 4.

I hope this makes sense.

I am using Excel 2007 and will need idiot proof instructions about how to implement a VBA solution if this is the only possible way of doing what I need.

Thanks in advance for your consideration of my question. The other teachers and secretaries at my school will never know how much of a help you will have been!


I was provided previously with the code for a VLOOKUPS function for a project of mine by JBeaucaire. It can be found here, in the returned file he provided to me: VLOOKUPS multiple results in a drop down list

The set up works wonderfully. But now I'm trying to accomplish something that is just a little bit different. I'm trying to to populate an array with all the results returned from an HLOOKUP formula. I was wondering if anybody had any code written that might create an HLOOKUPS function. I've tried to tweak the code provided for the VLOOKUPS function by JBeaucaire but I'm a novice, novice programmer and can only pull off simple functions and procedures.


I have a list of names that extend from cells D1:Y1. Below that in rows 16-22 are a list of tasks that the person could engage in, and in his relevant row would be a number pertaining to how many hours that task could be.

So essentially, i am hoping to find a formula that will lookup the person's name in row 1, and then sum the numbers in rows 16-22. Is this possible?

Thanks,
Ernie


Hi, I'm trying to do a HLOOKUP from a different workbook, basically it references dates on both sheets. Now I have noticed that on the sheet that the HLOOKUP formula is in, the date 20/08/2007 when not formatted to date is 37852, whereas the same date in the workbook being referenced is 39314, so the HLOOKUP is not working? why are the 2 dates different? If I open a new workbook the date is 39314. so i am assuming this is the correct figure. Why is my one workbook largely different?

Thanks in advance


Hello

I know that you can't use a HLookup to use any other row than the first as the lookup row, so is there a way you can use another excel formula (not macro) to lookup a value in the 8th row of an 8 row table (for example) and return a value from the 1st row?

Many thanks.


I need to count the head to head results in a poker league. There are 7 players and the league works with points being allocated for all places. I am interested in only the winner and second place. I want to count all the final (tournament poker format games work on a knockout basis until 2 players are left to play for first place) head to head results for each player against every other player. The spreadsheet is set out like a table with names in the leftmost column and then each games results in the adjoining column. Next to each name is the points they got for that game. I need to count each time a players get 10 points and match them to 7 point name for each game. This will give me the number of head to head wins that play has against an apponent. See image for layout of league. I may need to do a number of interim formulas to get the final results - thank you.


I have a table of data that has merged cells in the top row that each take up three columns. The problem I have is that when I do an hlookup on this table, the data returned will be from the first column of each data set (ie, column A, D, G, J etc). Is there a way to offset the hlookup to return data from the second column (i.e. B, E, H, K etc) without changing the structure of the spreadsheet?


Is there a way to use OFFSET (or something else) with a HLOOKUP formula?

I use the HLOOKUP formula to extract specific data into a seperate spreadsheet, but I have had to do a calculation in the cell next to the original data and now need to pull that amount into the seperate sheet. (I cannot put the new calculation above or below only beside).

I had thought that the OFFSET reference could be the HLOOKUP formula then I tell it to move over 1 column and return that amount.

Any solutions?

Thanks


Hi - Is there a way of inserting the row index number automatically into a Hlookup formula so that if I "drag" the Hlookup formula down the page it will automatically change the row index number accordingly?

Many thanks

B


I was reading the comments from the below topic and was very close to answering the question I had... but I need it to go a step further.

http://www.mrexcel.com/board2/viewto...lookup+vlookup


I have a table with the following layout:

1) Column A has an ID # (with same ID appearing on different rows)
2) Row one is the heading with a letter and can repeat across multiple columns.

I want to create a sumif using a vlookup and a hlookup, but I don't know how to combine the two and get the formula to work. For example in the list below, I want to sum everything that has ID6 and the columns with a B header.... I know this can be done using pivot tables, but I am trying to avoid this.........Please help! thanks

ID# A A B C A B
ID1 8 5 1 1 4 9
ID2 9 0 2 5 9 9
ID6 9 1 2 8 2 8
ID4 2 7 9 0 8 0
ID5 4 7 5 5 6 9
ID6 1 9 2 7 7 8
ID7 4 4 8 5 5 0
ID8 5 0 6 9 0 2
ID6 8 1 3 9 4 0
ID2 6 5 9 5 4 4
ID1 8 8 1 1 5 5
ID6 6 1 7 8 1 9
ID6 1 8 1 0 3 4
ID2 3 0 6 7 7 6
ID1 9 1 5 4 1 0
ID2 9 9 2 2 1 2
ID1 8 9 4 5 6 7
ID2 9 0 7 1 4 2
ID6 6 9 0 9 1 6
ID2 7 8 3 8 3 2


Kinda need help here.
I have this kind of code in my macro:

Code:

Dim str as string
str = "1693B_cdd"
For j = 10 To 18
    Range("B" & j).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & str & ".xls]Sheet1'!R12C10:R88C12,2,0)"
Next j


its working fine, but not the one that i wanted to be.
The problem is with the range, R12C10:R88C12, since my project need the range to be changed for every different file, so i need to put the 'range' value to a variable and i am trying to put the range value in a variable (which the variable value also changing as the file changed) but it wont works.
Anyone has idea??