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.

Download workbook here:

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

instead of vlookup?

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:

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:

Twitter:

LinkedIn:

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://artistmanagementaustralia.com/

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

Nice knowleddge and good pronounciation

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.

Thank you

excellent

Helo,

Dear Leila Gharani,

Please help to buil a dynamic and updatable calander in Excel….

Hi Ma'am, how did you enable Power BI in ribbon?

i love you. it has cleared me a great hurdle in handling a data of 752000 rows and 18 column data of every month. thank you very much.

Amount ID

47,901.61 156

47,901.61 157

614.40 158

Amount ID

47,901.61 156

47,901.61 156

614.40 158

Hi Laila, hope you are doing good. i used "Index and match" formula to pull the ID # in above data table, As amount 47901.61 is repeated , ID # are also pulled same which is 156( table-2), hope you understand my issue and how can i pull the id# 157 for the amount 47901.61 (second). Kindly solve it.

Just great, Thanks. Index was really tough for me but your way of explaining cleared all my doubts Thanks again

Thanks a lot. It is very easy to learn because you explained it in an outstanding manner. Thanks a lot.

Thank you very much! you taught me a very complex method in just a few minutes.

Absolute awesome lovely videos thanks a lot Leila, since I need to replace some functions like Vlookup or Hlookup to speed up my worksheet's calculations, 'cause I am buiilding the models in Office 365's excel version but the spreadsheets are going to be used/running in an old 2010 Excel (V 14.0) version, however, I love the new functions like Xlookup, filter, etc. but in Mexico many companies are still using old Office versions , thanks again IOU

Hi Leila, Please share the tutorial of INDEX and MATCH function one sheet 1 to Sheet 2 through VBA Macro.

thank you!

pleases provide the slicer .

Hi leila, what if i want to find the total profit of game?

What formula would you use, when you have 4 crête ria that than either be yes or no, but the combination of 4 can give a specific number?

The map example was helpful. Examples help. Thanks.

I am very comfortable with vlookup, IndexMatch,

But was glued to entire video looking at you and listening to your deliberation

Hi leila, This is RANGA FROM India. Your work is amazing…. I need a big guidance and help from you…. I have a bunch of data in a worksheet in i have name column and date column, In the new worksheet once i select name column, list of dates should come in the Date column and rest of the columns. Which function should i use… Im in Confusion. Looking for your guidance, Many Thanks.

I need to misty wash right now..

Can you show how to use index match to find data that has wrapped text please.

I want to be able to say type in a set of numbers in my lookup and have index match find it even if it is a wrapped text cell with other numbers in same cell.

To be able to call up all information within that cell that has been wrapped text.

If this is possible could you.make a video on this? I am stuck and cannot figure it out.

Right now I have to enter all numbers in wrap text format to find the data, but trying to avoid this by just typing one set of numbers and having it find that info and showing it all

if i have apps and i want to find out which division that belongs to, what function should i use.

Hi leila mam, the way you understand each syntax of each function and also results of such functions is versatile and very febulous. I have no words that how to i thanks you. Its really very superb.

Thank you very much 👌👌👌👌👍👍💐

Some issues with Index too though. Inserting columns in the dataset and updating links across books seems to be troublesome with index.

youre great. you are now my go-to for xls Qs. thank you!

You're awesome! Thanks!!

I an facing a small issue. I am using Index and Match function along with Small function to arrange my table in the increasing list of values.

For e.g. If the Value of A & B is 0, the Index and Match function shows A = 0 2 times instead of showing A=0 and B=0. How do i solve it ? Could you please help me solve it ?

Excellent thanks you.

Hello, I spent a weekend watching your excel videos. The week after, I was able to tackle some very complicated excel projects at work. Thank you and please keep posting

super, really it is very helpful