You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
(Note: In honor of VLOOKUP Week, March 25-31, we're re-publishing this round-up of VLOOKUP resources on Office.com and other places. Thought some of you might want to brush up on your skills before you write that post.)
People are crazy for Excel's VLOOKUP function, and why not? Among other things, VLOOKUP is known for its rugged good looks and unparalleled martial arts ability.
In all seriousness, VLOOKUP is one of the most widely used, versatile functions in Excel. You typically use it to quickly look up and extract specific bits of information from a big list of data. In the following example, I've instructed VLOOKUP to search for a page ID on another worksheet, find a corresponding page name, and plunk that page name back in the cell where I entered the formula. (Yes, "plunk.")
Since VLOOKUP is one of the top search queries on Office.com, we gather you have some questions about how to use it. Here's a compilation of resources that can help you learn or troubleshoot.
If you don't know much about VLOOKUP, start here.
Resource
What is it?
VLOOKUP: What is it, and when to use it
Video-based training course that contains detailed information about VLOOKUP, including a practice file and a Quick Reference Card.
VLOOKUP function
Help article that describes the formula syntax in detail and provides examples. Just the facts, ma'am.
VLOOKUP refresher
Printable card for those who use VLOOKUP infrequently and thus need help remembering the function arguments (in plain English, the information between the parentheses that tells the function what to do).
Excel Lookup Functions and Formulas - Beginning to Advanced
Video series that covers all the lookup functions in Excel, not just VLOOKUP. (If you don't know about Mike Girvin's "ExcelIsfun" video series on YouTube, take a look and see what you think. I find these videos to be immensely helpful.)
Once you dig in, you may run into issues. These resources can help.
VLOOKUP troubleshooting tips
Printable card that explains techniques for avoiding or managing #N/A errors.
Solutions to three common problems when using VLOOKUP
Post by MVP Greg Truby that addresses these very common questions:
Beyond VLOOKUP: INDEX and MATCH
Article by MVP Bill Jelen that explains a little more about that last bullet point above--that VLOOKUP always uses the first column in a lookup table as the starting point. If that won't work for your situation, you can use INDEX and MATCH instead of VLOOKUP.
Microsoft Answers
Free tech support forum where you can ask specific questions about VLOOKUP and get answers from Excel MVPs and other Excel users. For example, here are existing threads about multiple VLOOKUPs and conditional VLOOKUPs.
No doubt, there are countless useful articles and tutorials out there. If you have a personal favorite, or a good tip, please feel free to share it with us.
--Anneliese Wirth
Comments: (8) Collapse
Hallo sorry i dont know where else to ask this how can I remove duplicates from a worksheet that have been referenced by a equal sign in excel 2007. I tried filter advance filter and remove duplicates but they dont work.
Thank you
Hi 14637669:
Thanks for stopping by. That's quite a name you have there. :-)
What exactly happens when you try these methods? Do you get an error message?
You might try asking your question at Microsoft Answers. For example, here's a list of questions (and answers) about removing duplicate values:
answers.microsoft.com/.../Search
- Anneliese
I remove duplicates in 2 ways depending on length of table:
1 advanced filter.
2 find dup in col A using formula =A1=A2, copy paste values col with formula, order by col with formula now value, delete all rows with value 1
VLOOKUP is a powerful and extremely useful function, however the Windows "waiting" spinner appears when pasting multiple VLOOKUP formula cells from one spreadsheet to another. The hardware platform that I'm using has dual, quad-core CPUs, 8 GB of RAM, 1 GB of VRAM and dedicated GPU and the CPUs and memory have next to no activity when VLOOKUPS are being pasted. Also, the hard drive is not being accessed as everthing is cached in RAM and I'm running a Windows 7 64-bit environment.
What gives?
@smabres: Appreciate the additional tip about removing duplicates. Thanks for taking the time to leave it.
@timjs: This doesn't sound familiar, but it does sound frustrating. I'm digging into this on my end, but in the meantime, you might also ask your question at the Microsoft Answers site: answers.microsoft.com/.../excel. In the Excel forum there are support engineers, Excel MVPs, and community moderators who are poised to answer specific questions like this one. I'll loop back if I find out that this is some kind of known issue.
@timjs: Here’s a bit more information, from a friend in product support (thanks, Ryan!):
“Most of the issues we see in Excel 2007/2010 deal with an old way of making sure to get everything included in the VLOOKUP. Namely, using the entire column as a range (for example, C:H) instead of where your data actually lies (for example, C1:H5000). In Excel 2003, this wasn’t a big issue because this version had a smaller number of worksheet cells to look through. However, with the increased row and column limits in Excel 2007/2010, this may cause performance issues. In other words, with a formula like =VLOOKUP(B1,C:H,2,FALSE), every time Excel wants to calculate that cell it needs to look through about 6.3 million cells. If you have a VLOOKUP formula in thousands of cells, you will definitely see a performance hit.”
This may not be the root of your issue with pasting, Tim, but I think it’s worth sharing here, so that others can avoid potential performance pitfalls when working with VLOOKUP. Without seeing your workbook, it's hard to troubleshoot. Again, I'd send you to MS Answers and also refer you to these four excellent articles:
Excel 2010 Performance: Performance and Limit Improvements
msdn.microsoft.com/.../ff700514.aspx
Excel 2010 Performance: Tips for Optimizing Performance Obstructions (see the Lookups section)
msdn.microsoft.com/.../ff726673.aspx
Excel 2010 Performance: Improving Calculation Performance
msdn.microsoft.com/.../ff700515.aspx
Improving Performance in Excel 2007
msdn.microsoft.com/.../aa730921(v=office.12).aspx
Hope it helps.
Excellent VLOOKUP resources. Thank you.
Hi Richard -- Glad you liked them. Thanks for stopping by.
Comments: (loading) Collapse