Apr
19

How to perform VLOOKUP in Excel 2010: tips and tricks

Merge two tables in Excel easily

Excel's VLOOKUP function is used to find specific information that has been stored in a table of data. It has also been estimated that this function brings Excel beginners into panic. At the same time Excel power users can't live without it.

In this digest we offer you the best web articles about this feature.

Cascading validation lists

This VLOOKUP Great White Shark Award winning article can be seen as one of the most innovative ones. It shows how you can make basic cascading Validation Lists to use on your spreadsheets. You can take this example and apply it to your own real life situations.

Who needs VLOOKUP anymore?

In this post you will find at least two wonderful solutions:

  • Joining two tables in PowerPivot using the Create Relationship dialog box.
  • Once you merged two tables employing DAX function =REALTED in the PowerPivot grid to replace VLOOKUP.

Case sensitive LOOKUP

Be ready to attack a lookup action for a unique list of items, when upper or lower case is the only difference among the characters in the list. VLOOKUP will never differentiate CASE from cASe. The formula you will find in this post can.

Use consistent sheet names

If you consistently name the worksheets, you can combine VLOOKUP with INDIRECT to pull data from different tables. This technique is a life and time saver when you need to pull information from various sheets in a workbook.

How to lookup values to left?

VLOOKUP is undoubtedly useful formula. But it suffers from one annoying restriction. It won't go left. You will find the solution in this article.

OFFSET double lookup in PowerPivot

In this example you will see how to simulate the output achieved through double lookups using VLOOKUP, OFFSET and MATCH in PowerPivot.

Why 2 VLOOKUPS are better than 1 VLOOKUP

When you have a lot of data in Excel (like 100,000 or 1,000,000 rows) you often need to identify when a value you are looking for does not exist in the data. You can do this using some VLOOKUP formula.

Or you can use Merge Tables Wizard

Excel always leaves something that can be made a little easier. So if after exploring all the above tips you still find VLOOKUP scary, you can use our Merge Tables Wizard add-in that combines the standard Microsoft Excel Match, Lookup, or Vlookup functions.

Hope you'll find this helpful. Have a nice day!

See also

Post a comment



Please note that we are on holiday until Sep-16, so we won’t be able to reply to your comments for a while, sorry.
Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard