XLOOKUP vs INDEX+MATCH Function in Excel (Video in Khmer)

Hello everyone! Today I show you how to use XLOOKUP and INDEX+MATCH formula in Excel step by step with simple examples and video speak Khmer.

You ever want to lookup value from one table and return value from another column based on matching condition? Many people use VLOOKUP, but VLOOKUP have big limitation — it can only search from left to right! If your lookup column is on the right side, VLOOKUP cannot do it. That's why you need to use INDEX+MATCH or the modern XLOOKUP function!

Let's look at how to use both function below.

1. How to Use XLOOKUP Formula in Excel

XLOOKUP is the newest and most powerful lookup formula in Excel. It replace VLOOKUP, HLOOKUP, and LOOKUP. It can search left to right, right to left, top to bottom, and bottom to top!

Formula Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example:

Suppose you want to find Employee Name by entering Employee ID E102:

• Lookup Value: E102 (in cell G2)
• Lookup Array: A2:A10 (Employee ID column)
• Return Array: C2:C10 (Employee Name column)

Formula:

=XLOOKUP(G2, A2:A10, C2:C10, "Not Found")

If Excel find E102, it return the name. If not found, it show "Not Found" instead of #N/A error! Very clean and easy.

2. How to Use INDEX + MATCH Formula in Excel

Before XLOOKUP exist, INDEX+MATCH was the king of Excel formulas! It combine two functions together:

• INDEX: Return a value from a specific row and column number.
• MATCH: Find the position (row number) of a lookup value.

Formula Syntax:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

Example:

To find Employee Name by Employee ID E102:

=INDEX(C2:C10, MATCH(G2, A2:A10, 0))

• MATCH(G2, A2:A10, 0) find row number where E102 is located.
• INDEX(C2:C10, ...) get the name from that exact row number.

Watch video below to see full explanation in Khmer step by step:

💡 Quick Tip: If you use modern Excel (Excel 365 or Excel 2021), always use XLOOKUP because it is much simpler and faster to write. But if your workplace still use older Excel (like Excel 2013 or 2016), XLOOKUP won't work! In that case, INDEX+MATCH is your best choice because it work in all Excel versions.

📚 Useful Resources

• Top 15 Basic Excel Formulas Every Beginner Should Know
• Concat vs TextJoin Function in Excel (Video in Khmer)
• How to Count and Find Unique Values in Excel
• Essential Excel Keyboard Shortcuts Every User Should Know
• Free Download PDF - 50 Popular Excel Formulas Cheatsheet (Khmer Language)