Excel Magic Trick 778: INDEX & MATCH Lookup Functions Beginning To Advanced (18 Examples)

Download file: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT778.xlsx There are really about 30 example in this long video. INDEX and MATCH How To: 1 MATCH Functions Basics (about 0:46 minute mark) 2 INDEX Basics: Two way lookup (about 9:05 minute mark) 3 One way lookup vertical (lookup Left) (about 13:07 minute mark) 4 One way lookup horizontal (about 14:50 minute mark) 5 One way lookup with lookup column and match column orientated differently (about 17:51 minute mark) 6 INDEX & MATCH to do approximate lookup on table sorted Descending (about 19:15 minute mark) 7 Lookup whole row (about 21:41 minute mark) 8 Lookup whole column (about 24:49 minute mark) 9 Lookup Cell Reference (about 26:00 minute mark) 10 Create Dynamic Range For Chart (about 26:00 minute mark) 11 Lookup from more than one table when tables are on same sheet as formula (about 36:29 minute mark) 12 Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions (about 43:31 minute mark) 13 INDEX and ROWS to Filp a Table (about 47:20 minute mark) 14 Get Amount in Next Bracket (about 50:34 minute mark) 15 Select a Random Value (about 53:18 minute mark) 16 Lookup first non-blank value (about 54:24 minute mark) 17 Handle arrays that require Ctrl + Shift + Enter CSE (about 54:24 minute mark) 18 Basic lookup tool used in complex array formulas for extracting data (about 59:59 minute mark, 1 hour 1 minute 10 seconds)


  1. Thank you very much for your tutorials. I’ve learned so much from various videos. I have a question on section 9&10. I copied the exact formulas but sales returns B12 value for A11. Please help. Thanks again.
  2. 4:03 Match 7:12 Descending 22:00 zero ou empty row or column 47:50 Flip Table
  3. One word for this video...MAGNIFICENT
  4. I am learning excel following excellsfun from 6 months. It amazing. Excellsfun can please upload atleast one vedieo showing your face please. I am excited to see your the mind blowing teacher for excel.
  5. there was no sound , is it possible add sound too,
    its help all of us ,
  6. I have dates column sorted oldest to latest with missing dates for holidays and weekends. I want to return one month earlier date that are available in the list. I did it when dates are in descending order but couldn't find way when dates are ascending order. Any trick around?
  7. Is there any way around opposite sorting. I mean sorted descending but approximate match 1st biggest and jump back.
    Also is there any way to lookup for 2nd biggest?
  8. You are god father of every excel tutor/learner
  9. great tutorials my brother. U are my nr one source when issues at work
  10. 1:09:17 does this mean you can limit the number of students scoring a 99 to 3 students? i mean.. lets say u have N=10, or N=20 students, and you can actually get 20 students scoring 99 . Can you make the columns expand to N columns? 10 or 20... u get my point? at the moment, it seems like a max of 3! thank you
  11. Question on example #2 conditional formatting: let's say column headers contain text instead of numeric values (replace 1, 5, 25, 100 with A, B, C, D in this example). Conditional formatting to highlight discount value in a table only works if columns are in alphabetic order?
  12. Thanks!! 57:52
  13. best person ever to teach you how to use excel... love from Brazil...
  14. This is great. I have a quick question. What if on your rainfall rate chart you wanted to return the pipe size and were providing the area and rainfall rate? The table is exactly the same but you want the output to be the pipe size so it would have to pick the next largest pipe size. Would index match be the best approach and can this even be done? See 19:17
  15. Hi, I have followed your videos for years and learnt quite a lot from them...I am very happy with
    your explanations...Anyhow, I am now a bit puzzled about trying to mix SUMIF,INDEX and MATCH...I have followed your EMT 778 but cannot get my answer..How can I contact you to email you my spreadsheet please. This is to do with depreciation of fixed assets with cost centres...Could you help?...Many thanks....Alfa Romeo
  16. Sorry but 12 mins in and all I heard were words which meant nothing to me. 😯 All I want to do is search for account number original balance data from a seperate spreadsheet and place it in new columns in another spreadsheet aligned with the same account numbers.
  17. love love your videos. actually teaching people advanced stuff when you're done with the basics and want to learn more. you are a gentleman and a scholar sir! genius!
  18. Man I need you help!!!

    I’m having issues using index and match, it’s not giving me my information but only a couple of rows even if all the file is in the same layout... please help!
  19. Fantastic!
  20. This is a great way to use INDEX, MATCH, VLOOKUP and CHOOSE Functions. Why do you not use Named Ranges and refer to Absolute Ranges?  Wouldn't Named Ranges work much better or be easier to understand?  $$$$ signs in formulas everywhere can make things messy and confusing. Just a thought. Thanks for all your awesome Excel techniques.

Additional Information:

Visibility: 681265

Duration: 0m 0s

Rating: 2355