# How to use Excel Index Match (the right way)

Check the Excel Essentials Course:

Quickly learn all you need to know about INDEX & MATCH to get a quick start.
Complete Excel Dashboard course:

Check out my other courses below.

How to do Index Match in Excel: The basics. Watch this video tutorial to find out how you can use Index & Match for more complex lookup problems. This video shows you how Index Match works with simple and to the point explanation. It first shows you how Index works on it’s own and then how Match works and then puts the two Excel functions together.

Why index match is better than vlookup & why use index match

Here are 3 reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions – left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.

All in all, INDEX and MATCH is more flexible than VLOOKUP.
Here are some of the reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.

Note: Index match is not case sensitive

Index explained:

The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.
The syntax of INDEX is:

• The range where the return value resides.
• Number of rows to move down (the row index)
• Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range.

The MATCH function’s syntax is as follows:

• Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula.
• Lookup_array: The “list” (range) where the return values are located.
• Match_type: 0 for an “exact” match.

Index MATCH used together:

When INDEX and MATCH are used together, the MATCH function finds the look up value’s row / column index and then hands this value off to the INDEX function to get the lookup value.

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

Time Stamps:
00:00 Is INDEX & MATCH the same as VLOOKUP?
00:52 Excel INDEX function explained
05:07 Excel MATCH function explained
06:29 Using INDEX & MATCH together
08:08 Two way lookup with INDEX & MATCH (data validation)
10:30 How to REALLY learn Excel INDEX & MATCH

GEAR
Screen recorder:
Main Camera:
Backup Camera:
Main Lens:
Zoom Lens:
Audio Recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://cunglamdep.org

Xem thêm bài viết khác: https://cunglamdep.org/tong-hop/

## 31 thoughts on “How to use Excel Index Match (the right way)”

1. ed b says:

This is amazing. I just started a new job this week and I never used Index and match before. This helped me out a ton. Thanks

2. Ahmed Abdelfatah says:

Always get that error #N/A

3. Stride Wang says:

OMG this explanation indepth is amazing! Im a data science student and index match was my first confusion taking excel course. U helped me out!

4. parikshit srivastava says:

Awsome teacher you are.

5. mike mike says:

ONE QUESTION
I HAVE TWO TABLES
AS SHOWN
1 2 3 5 1 2 3 4 5
…..
LEFT SIDE 4 COLUMNS RIGHT TABLE 5 COLUMNS
THOUSANDS OF ROWS
I WANT TO FIND IN RIGHT TABLE HOW MANY TIME AND WHERE IS EXACT MACHT OF LEFT TABLE
IN ALL ROWS
WHICH FUNCTION TO USE PLEASE AND HOW
THANK YOU

6. mike mike says:

@UC53aEw-bHeHz6OC7VauehnQ

7. davide says:

totally agree.. i had to force myself too.. but i feel it is the right choice.. tnx :))

8. RR Brothers says:

Looking your video, your taking more time to show the formula, so we go to look others,,
Next time take 3mt for that

9. Wayne Ackerman says:

I really like her presentation style. After she completes something, she always goes back to show why it works !!!! Not all excel lectureres have figured this out.

10. Deddy Wirata says:

Thank you!!! Your explanation makes this much easier

11. mark allen Idos says:

hi there, is there anyone who can teach me how to mail merge "many to one" row datas? i've been searching for some method buts they seem to be complicated and some requires ad ons on excel.

12. Kavita Behera says:

It is a very helpful video. Thanks

zabardast

14. Saminda Chathuranga Bandara says:

Superb explanation. I got this perfectly only by watching this Video. It's very clear and easy to understand. It's really appreciated and thank you vey much Ms. Gharani.

Thank u mam love from india

16. Luis Heimpel says:

I used index/match with an excel table. When I created the table, excel called it table1. I named it Materials. When using match with the range Materials, it gave me an error. When I replaced Materials with Table1 then it worked. Why? Index does work with both names.

17. jhanzaib bukhari says:

It's amazing, I'm using it for many years but never had such great concept as I got today. 🙂

18. SpendsTooMuchOnKpop says:

What if I wanted to find the Apps of a given Division?

Awesome video, Thanks alot!!!!

20. Jeff Peebles says:

21. Babak Tf Tf says:

لیلا عزیز. خیلی ممنونم از فیلمهای آموزشی شما. این فیلمها برای یاد گرفتن آفیس به خصوص اکسل برای من خیلی کمک کننده بود. امیدوارم مانند همیشه در این مسیر موفق باشی

22. Gia Ngo says:

Thank you so much! Your explanation is very easy to follow and understand. I am a recent grad and will be working next week! It is my first real job and my manager advised me to learn how to use index and match. The other videos and explanations that I looked up were so complicated and hard to understand until I found you! I also watched your other videos and downloaded your workbooks for practice as well. Very helpful! thank you!

23. Jerrel Ajoeb says:

In short: Index is used to find the referred row value (match) in a referred column of the defined domain.

24. PooiWai Kok says:

Hi.. Why excel row height changes between computers. Both computers using same version of OS and Ms Office.

25. Monica Liu says:

Love you sweet! So nice of you !

26. Phannita Vongphanakhone says:

Nice knowleddge and good pronounciation

27. Afroz Ahsan says:

The simple way I always thing about index and match is as follows:

We need to make sure we know the followings:

A = 'What to look for, the input if you like"

B = 'In which list we can find the A'

C = 'The answer we want to get back once we have found the item in list B or the output'

Then just slot in the list references for lists B and C and the cell reference for A in the formula below.

=INDEX(C, MATCH(A, B, 0))

Basically for those moving from vlookup, where you would go in the order ABC so = VLookup(A,B,C(How many lines down),0) So here in "Index and Match" it is CAB instead of ABC.

28. AMAN JAJOO says:

Thank you

29. A.A .J says:

excellent

30. A Rehman says:

Helo,
Dear Leila Gharani,