INDEX MATCH Explained (An Alternative to VLOOKUP)

Tổng Hợp



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/

36 thoughts on “INDEX MATCH Explained (An Alternative to VLOOKUP)

  1. 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.

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

  3. 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.

  4. 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.

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

  6. 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

  7. 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

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

  9. 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.

  10. =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)),"")), "")

  11. 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:)

  12. 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…

Leave a Reply

Your email address will not be published. Required fields are marked *