Got data? Then you need lookup formulas! As a spreadsheet expert and former accountant, I've relied on Excel lookup functions for ages. But XLOOKUP is shaking things up. In this comprehensive guide, we'll compare the tried and true INDEX/MATCH duo vs the upstart XLOOKUP in depth. Ready for an Excel formula face-off? Let's rumble!
Kamusta my data-loving friends in Manila!
A Lookup Refresher
Let’s quickly cover the basics of different Excel lookup functions:
· VLOOKUP - Fetch columns to the right. Requires sorted lookup column.
· INDEX/MATCH - Powerful 2-way lookups. Need 2 formulas.
· XLOOKUP - Newer 2-way lookup in 1 formula. Excels at unsorted data.
Now let’s dive deeper...
The Classic: INDEX/MATCH
INDEX/MATCH has been the gold standard for flexible lookups in Excel. But it takes two formulas working together:
· Match Rows with INDEX/MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_array, 0))
** MATCH finds the row match, INDEX returns the value.
Breaking down the example formula:
=INDEX($L$14:$L$8820,MATCH($AC$17,$C$14:$C$8820,0))
The INDEX formula returns a value from a range based on a row and column number.
The MATCH formula finds the relative position of a lookup value in a range.
So this formula is looking up the value in cell AC17 within the range C14:C8820, getting the row number where it finds a match, and using that row number to return the corresponding value from the range L14:L8820.
In simple terms, it searches column C for the value in AC17, gets the matching row, and returns the value on that row from column L. The 0 at the end specifies an exact match is required.
· Match Columns with INDEX/MATCH
=INDEX(return_column, MATCH(lookup_value, lookup_column, 0)
**Same idea but with columns instead of rows.
Breakind down the formula:
=INDEX($C$14:$C$8820, MATCH($AC$27,$A$13:$L$13,0))
This is an INDEX MATCH lookup formula.
The MATCH part searches the range $A$13:$L$13 for the value in cell $AC$27, and returns the relative column number where that value is found.
The 0 at the end means it requires an exact match in that range.
The INDEX part uses that column number to return the entire column data from the range specified - $C$14:$C$8820.
So in simple terms:
MATCH finds the column where $AC$27 is found in $A$13:$L$13
INDEX returns the entire column data from that column in $C$14:$C$8820
Together it looks up the column header in $AC$27 within the header range $A$13:$L$13, gets the matching column number, and returns the data from that entire column in the range $C$14:$C$8820.
So it's an INDEX MATCH horizontal lookup that returns an entire column of data based on matching the lookup value to the column headers.
Pros
· Precise exact matching
· 2-way lookups in both directions
· Can look left or right, up or down
· Can be used in older versions of Microsoft Excel
Cons
· Requires 2 formulas (Not a big deal)
· Updating lookup ranges when new data added (But there is a trick with what you can avoid it)
· Slower calculation times
So in summary, INDEX/MATCH is extremely powerful but requires more setup work.
The Challenger: XLOOKUP
XLOOKUP combines the capabilities of VLOOKUP and INDEX/MATCH into a single function with new tricks up its sleeve:
XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array)
· lookup_value - the item you want to search for
· lookup_array - table/range to search through
· return_array - column with data to return
In our Example
Which one is easier for you?
Breaking down the formula:
=XLOOKUP(AC37, $C$14:$C$8820, $L$14:$L$8820)
This formula is using the XLOOKUP function to perform a vertical lookup.
It works like this:
· AC37 contains the lookup value
· $C$14:$C$8820 is the lookup array or range - this is the column it will search through
· $L$14:$L$8820 is the return array - this is the column it will return values from
So in simple steps:
· XLOOKUP searches down the lookup array $C$14:$C$8820 for the value in AC37
· When it finds a match, it takes the row number of that match
· It uses that row number to return the value from the same row in the return array $L$14:$L$8820
In summary, it looks up the value in AC37 in the first column range, gets the matching row, and returns the value from that same row in the second column range.
So it's performing an exact vertical lookup to return values from one column based on a lookup value in another column.
XLOOKUP Pros
· 2-way lookup in a single formula
· Handles sorted or unsorted data
· Expandable dynamic lookup arrays
· Faster calculation times
· Spills results for easy drag-down
· Clean and simple syntax
XLOOKUP Cons
· Slightly more complex for exact matches
· Not as widely adopted yet
· Requires Office 365 or latest Office version
When To Use XLOOKUP
Since XLOOKUP doesn’t require sorted data or fixed ranges, it’s great for:
· Frequent data updates
· Quick lookups on unsorted dumps
· Simpler syntax for beginners
· Future-proofing lookup formulas
And the Winner is...
While INDEX/MATCH is a veteran lookup solution, XLOOKUP simplifies 2-way searches in one formula. And it doesn't require sorted data or fixed ranges. This means less setup and maintenance.
For these reasons, I declare XLOOKUP the champion of Excel lookup formulas! Its flexibility and concise syntax can't be beat but I still stick with the lovaly IINDEX/MATCH you know for those with older versions and who doesn’t want to pay for Office 365 Membership.
Examples Comparing XLOOKUP and INDEX/MATCH
Let's walk through some examples...
Two Dimensional lookup.
INDEX/MATCH
=INDEX($A$14:$L$8820,MATCH($AC$47,$C$14:$C$8820,0),MATCH($AC$48,$A$13:$L$13,0))
1. The INDEX function returns a value from a range of cells. It needs two pieces of information:
o The range of cells to lookup in: $A$14:$L$8820
o The row and column number within that range to return
2. The first MATCH function finds the row number. It looks for the value in AC47 within the range C14:C8820. It returns the row number of the exact match.
3. The second MATCH function finds the column number. It looks for the value in AC48 within the range A13:L13. It returns the column number of the exact match.
4. Those two row and column numbers are fed into the INDEX function as the row and column number to return a value from.
5. So in the end, the INDEX function returns the value at the intersection of:
o The row matched from AC47
o The column matched from AC48
o Within the overall lookup range of $A$14:$L$8820
6. This allows you to lookup a value in a table using both a row and column header to uniquely identify the data.
7. For example, if:
· AC47 contains the value "Stranger Things"
· AC48 contains the value "description"
· $A$14:$L$8820 is a table of Netflix Content data by movies or series
Then the formula would return the description figure for Strange Things.
XLOOKUP
=XLOOKUP($AC$59, $A$13:$L$13, XLOOKUP($AC$58, $C$14:$C$8820, $A$14:$L$8820))
1. This formula has two XLOOKUP functions nested together:
o The inner XLOOKUP does a vertical lookup:
o It searches down $C$14:$C$8820 for the value in $AC$58
o When it finds a match, it takes that row number
2. It uses the row number to return the entire row of data from $A$14:$L$8820
o The outer XLOOKUP does a horizontal lookup:
o It searches across $A$13:$L$13 for the column header $AC$59
o When it finds the match, it takes the column number
It returns the column of data looked up by the inner XLOOKUP
In summary:
· Inner XLOOKUP vertically looks up a row
· Outer XLOOKUP horizontally returns the column
So it performs a two-dimensional lookup to return an entire column of data based on matching both the row and column headers.
I hope these examples shed light on when to use XLOOKUP vs INDEX/MATCH. While the classic INDEX/MATCH still has its place, XLOOKUP offers a more modern approach for many use cases. Mastering Excel lookups provides unlimited data analysis potential to help you Excel!
If you found this guide useful, please bookmark it for future reference. Share it with colleagues who are looking to upgrade their lookup game. And be sure to follow me on social media for more data insights and Excel tips! I appreciate you taking the time to learn with me. Maraming salamat and see you in the next tutorial!