How to perform VLOOKUP in Excel - 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

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com