Ever wonder what is the fastest lookup function in Excel? In this experiment style video, I test VLOOKUP vs. INDEX+MATCH vs. XLOOKUP by writing 11 million formulas. The results are surprising!!!
Note: there is no sample file for this video (the dataset is rather large, but feel free to make some random data in Excel to test the concepts yourself).
⏱ In this video:
=============
0:00 - Fastest lookup formula in Excel!
0:26 - Dataset and testing methodology
2:31 - Writing the VLOOKUP formula
3:34 - INDEX MATCH formula
4:34 - Faster INDEX MATCH formulas
6:32 - XLOOKUP formula
7:16 - The results (calculation time)
10:51 - File size comparison
11:48 - What does it all mean?
🧪💻About my computer:
======================
Any test results should be viewed in conjunction with the equipment used. So here is my computer configuration.
Operating System: Windows 10
Office version: Excel 365 Insider Beta channel (version 22xx)
Processor: Intel i5 @ 2.90 ghz
Ram: 16 GB
GPU: Dedicated GPU (Nvidia Quadro P400 2GB)
🏃♂️FAST Excel Add-in:
=================
If you need to profile, audit or speed up your workbooks, definitely check the Fast Excel add-in. [ Ссылка ]
#notsponsored
Other ways to combine data
=======================
Lookups are not the only way to combine two tables of data. You should try below options to speed up or simplify your data combines.
◉ Combine two tables with Power Query - Video: [ Ссылка ]
◉ Using data model and relationships (Power Pivot) Article: [ Ссылка ]
◉ Using SQL
Learn more 😎
============
👌 Must have Excel formulas:
XLOOKUP - [ Ссылка ]
FILTER - [ Ссылка ]
IF - [ Ссылка ]
SUMIFS - [ Ссылка ]
Top 10 formulas - [ Ссылка ]
😍 Other Excel features:
Conditional Formatting - [ Ссылка ]
Power Query - [ Ссылка ]
Pivot Tables - [ Ссылка ]
#vlookup
