Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data.
But we shouldn't forget about its lesser-used sibling, HLOOKUP.
First, VLOOKUP. For those not in the know, it allows you to look for a value in a range, and bring back a value associated with that value from the range in which you're looking. So you may want to have a cell in which you select an employee's name from a dropdown and for their salary to appear in the next cell. Or you may want to bring back the stock levels for a range of stock IDs.
Below is the syntax of the VLOOKUP function:
=VLOOKUP(cell,range,number,boolean)
The cell is the thing that you want to look up. The needle, if you will. The range is the area in which you want to hunt for it. The haystack if you will. The number is the column number in the target range that you want to return. And the boolean is an indicator to tell Excel whether to only find exact matches (0) or to also return close matches (1). (In my 16 years of using Excel, I've never once set this value to 1.)
The main two rules to understand are:
- The thing being looked up must appear in the first column of the range in which you're looking it up
- The number is the column number containing the desired data. So if your range runs from column B to column G, setting this to 3 will bring back data in column D (column B is 1, column C is 2, column D is 3). It must be positive, so the data you return must be to the right of the column you're looking up in.
So take the following formula:
=VLOOKUP(A1,$G:$Z,4,0)
This will take the value in cell A1, look for it in column G (the first column of the range), and bring back the associated data item from column J (the fourth column of the range). If A1 appears more than once in column G, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.
The range doesn't have to be a full set of columns, by the way. It can instead be a square range.
So now to the HLOOKUP.
It's exactly the same, apart from rows and columns being switched.
=HLOOKUP(A1,$7:$26,4,0)
This will take the value in cell A1, look for it in row 7 (the first row of the range), and bring back the associated data item from row 10 (the fourth row of the range). If A1 appears more than once in row 7, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.
I find I rarely use the HLOOKUP function. But it's comforting to know it's there if I might need it. Which I did today.
My friend Will had a bunch of columns representing sequential dates, each row representing a milestone. There would be a single X appearing where those milestones were to hit. He wanted that milestone date appearing as a column next to the milestone name.
The solution involved replicating the date headings at the bottom of the range, given the rule stipulating that the data you need to return must be below the row you're looking up in. We put it in row 100.
And the solution was to look up the value X in the row in which the formula was appearing, and bring back the equivalent value in that newly-created row 100. So in row 5, we put the following formula:
=HLOOKUP("X",5:$100,101-row(A5),0)
Let's break that down.
- X. This is the thing that is being looked up.
- 5:$100. This may look odd. It's a reference to rows 5 to 100. The $ against the 100 will keep it fixed when the formula is copied down to subsequent rows. But by not having a $ against the 5, it will float. So copying that same formula down to row 6 will make it change to 6:$100.
- 101-row(A5). This takes the current row number away from 101. So in this case, yields 96. You want to return the 96th row of data (in this case row 100). By making this a variable, it changes when the formula is copied to the other rows in the spreadsheet. Row 99, for example, will want to bring back the second row's data (i.e. still that in row 100)
So there you have it. VLOOKUP and its lesser-known sibling, HLOOKUP.
Hi,
I have a excel sheet which have the data of stock market for last 13 years. I want to filter and just want data of last Thursday of every month. can any one guide me over this
Regards
Hi Parag,
The formula below should work to figure out whether a date is the last Thursday in the month.
Dan.
=IF(WEEKDAY(A5,2)<>4,"No",IF(OR(MONTH(A5)=1,MONTH(A5)=3,MONTH(A5)=5,MONTH(A5)=7,MONTH(A5)=8,MONTH(A5)=10,MONTH(A5)=12),IF(DAY(A5)>=25,"Yes","No"),IF(OR(MONTH(A5)=4,MONTH(A5)=6,MONTH(A5)=9,MONTH(A5)=11),IF(DAY(A5)>=24,"Yes","No"),IF(MOD(YEAR(A5),4)=YEAR(A5),IF(DAY(A5)>=23,"Yes","No"),IF(DAY(A5)>=22,"Yes","No")))))
you nerd!
Hi,
I want to know how to match the value in column A with a value in column D (if there is a match); take the corresponding description of column D, which is listed in column C, and transfer it appropriately to column B.
For example:
A B C D
123 Green 999
456 Blue 321
789 Red 987
987 Yellow 888
654 Brown 555
321 Black 123
Once 321 in column A is matched with 321 in column D, it transfers Blue to the last row in column B.
I hope this makes sense and you can help me. The closest I have come is:
=IF(ISNUMBER(MATCH(A1,D:D,0)),C:C,"")
But it copies the color from C to B, adjacently, so it would find 321 in both columns and transfer black over to the last row in column B.
Please HELP! 🙂
Ashley
Hi Dan, I wondered if you could help. I look after accommodation and travel for my clients and I am looking for a formula that will generate rooming lists. I use codes for each hotel I use and I add these in the interesection between the name of the guest and the date they are staying at the hotel. This automatically updates my total per hotel and per guest but I can't work out a way of generating rooming lists easily. Is there a formula that I can use easily? Something like if excels finds a specificied code for a specified date - could it return the name of the guests? Thanks
Hi Marco. I had a little go at this and will email you the outcome. It's not overly pretty 🙂
Basically you need to create the construct for where to look. So by selecting a date, you're telling it where to start the VLOOKUP. And then you need to figure out how many rows to count across.
Let me know if you have any questions about the file I send.
Dan.
Hi Dan,
I am trying to create a function whereby users of my spreadsheet can type in 1-3 words to search a range of parts descriptions (text entries) and pull up their associated parts numbers and descriptions.
The intent is that they don't know the part number to order or the exact description, so they enter up to three words and it returns all the part numbers with those three words (or some of those words) and the descriptions of thos parts. Hopefully that makes sense.
I don't want it to be case or context sensitive. Is there a way to do this in excel?
Thanks,
Sam
Hi Sam,
I've sent a solution to you by email. Best,
Dan.
Bummer, that would have been a nice one to see!
Hi Please
Please assist
I have 2 sheets, the first sheet is a vendor sheet in Column A is all my Vendor Codes and in column B is my Vendor names
then on my second sheet i have a table with all details, but only the vendor names is on there... what formula can I use to extract the correct vendor nr for that specific vendor in CELL D9 on my (expense sheet)
Hello,
I've found your articles very good.
i just need help with something very easy.. i believe.. but i cannot get my head around it.
I would need a formula that uses the data from table 1 (column B). Need to import it in table 2. As you can see, in table 2, my column A has the same value 3 times ( that's how i need it) . And i would like your help with the VLOOKUP formula. If I would import the data I would need it only for the first value, not all three of them.( and for the following 2 I would need 0 value) Is that possible? ( example of how i need it in table 3)
Waiting for your feedback.
Thank you in advance for your help
TABLE 1
A B
300004 4.6
305934 34.5
305928 16.1
300017 30.475
306244 24.725
305985 4.025
306254 3.501
306253 6.613
306256 5.446
306363 7.78
TABLE 2 using formula: =VLOOKUP($B$4:$B$29,Sheet1!$B$3:$C$19,2,0)
A B
300004 4.6 KG 1
300004 4.6 KG 2
300004 4.6 KG 3
300017 30.475 KG 4
300017 30.475 KG 5
300017 30.475 KG 6
300024 #N/A KG 7
300024 #N/A KG 8
300024 #N/A KG 9
What i would need would be:
TABLE 3
A B
300004 4.6 KG 1
300004 0 KG 2
300004 0 KG 3
300017 30.475 KG 4
300017 0 KG 5
300017 0 KG 6
300024 #N/A KG 7
300024 #N/A KG 8
300024 #N/A KG 9
hi
I am getting close to the formula
i want to enter a serial number in Column B1:B23 page 1
then it matches serial number page 2 column A:1:a23
than data(written and date) from page 2 column b,c shows up in page 1 column c,d
i cant get it without error and both columns showing up
I want to be able to copy an Hlookup formula down.
The first one would be like this =HLOOKUP(E354,'Price List'!$EB$185:$EE$194,6,FALSE)
The next one down would be =HLOOKUP(E354,'Price List'!$EB$185:$EE$194,7,FALSE)
Is there a way to do this where the range number would automatically change without me having to change it manually. If the example above, the 6 going to 7 on its own.
Hi Dan,
I'm fairly new at using vlookup and I'm having trouble figuring out a multi column lookup.
I am trying to create a function to lookup a code from 2 columns of data. As I type in the information in Columns A & B, I'd like to get the possible corresponding codes to select from and populate into column C.
Column A - Template Name
Column B - Template Value
Column C - Corresponding code
I'm trying to pull column C from another worksheet in my workbook where all 3 columns are populated with a full listing of corresponding codes.
I hope this makes sense. Please advise.
Kind Regards,
Grace