In this video I explain how to use the INDEX MATCH formula as an alternative to VLOOKUP in Excel. Download the Excel file:

You can follow along with the video and practice writing the formulas. The Excel file also includes my cheat sheet for writing the formulas.

I explain the two main benefits of INDEX MATCH over VLOOKUP. This includes:

The ability to return a value from a column to the left/above.

Separate columns for the lookup and return ranges.

We also look at the most common error that the formula returns and how to prevent it.

The new XLOOKUP function is another alternative to VLOOKUP and INDEX MATCH. Check out my other videos for more details on that.

VLOOKUP Explained:

XLOOKUP Explained:

XLOOKUP Explained in 3 Minutes:

Excel Tables for Beginners:

#MsExcel #ExcelCampus

Nguồn: https://cunglamdep.org

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

The index+match (parentheses) made me think about my algebra lessons

Very nice explanation, covering the basic functions of INDEX & MATCH formula. I will use this function in place of the VLOOKUP formula. Thank you for sharing your sample Excel worksheets. It will be of great help for practice.

Very clear explanation.understood it very well. Thanks so much.l was trying to get this concept and got it noe

This is such a great tutorial on INDEX MATCH. I love that this broke down each function's definition and even put the requirements in layman's term for anyone without coding experience. The snippet about using data validation for pulling up values was brilliant. Definitely is another great idea for my toolbox. Thank you for the awesome, well-articulated video.

Thank you so much for delivering a clear and well paced explanation of these functions. One of the best videos of this nature I've seen.

loved your explaination, simplicity, very professional

after a week, you are the only one I got the index& match function with data validation from,,BIG THANKS

Hi Jon

Another brilliantly portrayed tutorial which when I put into my spreadsheet worked a treat. However The results that I am looking to get involve looking up a referee in a list and returning the date(s) which they officiated at the games specified but in each instance of the lookup it just sees the first time the ref "reffed" and keeps returning that date down the list

Is there someting you can insert into the formula which will ignore the already matched name and move on to the next instance of that referee

James

Excellent video. What if you have duplicate results and rows that are blank?

best excel instructor!!!

thank you . its really useful.

Great video !!

HI GUYS AND LADIES

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

Great video!!!! Very informative, great examples, easy to comprehend and laid out clearly. Great work Jon!!!!!

Can you make a sheet on X look up

Brilliant

Great vdo, easy to understand how to use them.

Hi Jon. Thank you for your wonderful video. Only one question, please. Will this works if I want to get results (In index array column ex. Asset numbers) but I have multiple same look up value in the row (ex: "Transaction") and in column look up value (ex. "Posted") using the Match formula? Meaning I need to get all the asset numbers with "Transaction and "Posted" criteria? I tried it but struggling to get my desired results giving me only the last item on the list. Maybe I missed something. Sorry, only interested to explore if only possible.

I am getting a #N/A error

Well Done

=IF(AND([@[Available in Budget]]="YES", [@[Submitted Correctly]]="✔"), IF(IFERROR(INDEX('[Bill Payments.xlsm]Payments'!$E:$E,MATCH([@[Transaction ID]],'[Bill Payments.xlsm]Payments'!$Z:$Z,0)), "")=0,"Awaiting Payment",IFERROR(INDEX('[Bill Payments.xlsm]Payments'!$E:$E,MATCH([@[Transaction ID]],'[Bill Payments.xlsm]Payments'!$Z:$Z,0)),"")), "")

Xlookup?

Brilliant!

Ease to understand, Awesome.

Thank you very much!!

Another good one!

What a crystal clear explanation 👏 👏

Bullseye, Simple, Crisp, TothePoint. You Rock Jon – keep up the good work

Thank you sir.

You are the best person to explain this. with simple and clear example. Much much appreciated!!1

Thank you. Your video was a big help.

brilliant!

I have struggled with index and match for years – this is the simplest explanation on the internet – hands down the concept is now crystal clear . If you're ever in Sydney buzz me – I owe you a coffee Jon:)

Very much helpful. Thanks.

Simply great explanation. Thank you!

Thanks for explaining such a complex function in a simple way.

The Vlookup formular works on different sheets but it only works on one condition situation. The index match works on more than one conditions but not works on different sheets….. just drive me crazy…