Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Thermometer Chart in Excel
  • How to add Trendline to a chart in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Chart Axes in Excel
  • How to create a Histogram in Excel

References

  • Last row number in range
  • How to get last row in mixed data with blanks in Excel
  • How to get address of first cell in range in Excel
  • Basic INDEX MATCH approximate in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation must contain specific text
  • Excel Data validation no punctuation
  • Prevent invalid data entering in specific cells
  • Excel Data validation with conditional list

Faster VLOOKUP with 2 VLOOKUPS in Excel

by

This tutorial shows how to calculate Faster VLOOKUP with 2 VLOOKUPS in Excel using the example below;

Formula

=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())

Explanation

With large sets of data, exact match VLOOKUP can be painfully slow, but you can make VLOOKUP lightening fast by using two VLOOKUPS, as explained below.

Notes:

  1. If you have a smaller set of data, this approach is overkill. Only use it with large data sets when speed really counts.
  2. You must sort the data by lookup value in order for this trick to work.
  3. This example uses named ranges. If you don’t want to use named ranges use absolute references instead.

Exact-match VLOOKUP is slow

When you use VLOOKUP in “exact match mode” on a large set of data, it can really slow down the calculation time in a worksheet. With, say, 50,000 records, or 100,000 records, calculation can take minutes.

Worked Example:   VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Exact match is set by supplying FALSE or zero as the forth argument:

=VLOOKUP(val,data,col,FALSE)

The reason VLOOKUP in this mode is slow is because it must check every single record in the data set until a match is found. This is sometimes referred to as a linear search.

Approximate-match VLOOKUP is very fast

In approximate-match mode, VLOOKUP is extremely fast. To use approximate-match VLOOKUP, you must sort your data by the first column (the lookup column), then specify TRUE for the 4th argument:

=VLOOKUP(val,data,col,TRUE)

(VLOOKUP defaults to true, which is a scary default, but that’s another story).

Worked Example:   Get nth match with VLOOKUP in Excel

With very large sets of data, changing to approximate-match VLOOKUP can mean a dramaticspeed increase.

So, no-brainer, right? Just sort the data, use approximate match, and you’re done.

Not so fast (heh).

The problem with VLOOKUP in “approximate match” mode is this: VLOOKUP won’t display an error if the lookup value doesn’t exist. Worse, the result may look completely normal, even though it’s totally wrong (see examples). Not something you want to explain to your boss.

The solution is to use VLOOKUP twice, both times in approximate match mode:

=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())

How this formula works

The first instance of VLOOKUP simply looks up the lookup value (the id in this example):

=IF(VLOOKUP(id,data,1,TRUE)=id

and returns TRUE only when the lookup value is found. In that case,
the formula runs VLOOKUP again in approximate match mode to retrieve a value from that table:

VLOOKUP(id,data,col,TRUE)

There’s no danger of a missing lookup value, since the first part of the formula already checked to make sure it’s there.

Worked Example:   Self-contained VLOOKUP in Excel

If the lookup value isn’t found, the “value if FALSE” part of the IF function runs, and you can return any value you like. In this example, we use NA() we return an #N/A error, but you could also return a message like “Missing” or “Not found”.

Remember: you must sort the data by lookup value in order for this trick to work.

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • How to use Excel NOT Function
  • IF, AND, OR and NOT Functions Examples in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Find Last Day of the Month in Excel
  • Pad week numbers with zeros in Excel
  • How to get workdays between dates in Excel
  • Get day from date in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Categorize text with keywords in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to Create Calendar in Excel
  • How to create dynamic worksheet reference in Excel
  • With vs Without Array Formula in Excel
  • List sheet names with formula in Excel
  • 231 Keyboard Shortcut Keys In Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning