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)

Comments

  1. great tutorials my brother. U are my nr one source when issues at work
  2. 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
  3. 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?
  4. Thanks!! 57:52
  5. best person ever to teach you how to use excel... love from Brazil...
  6. 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
  7. 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
  8. 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.
  9. 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!
  10. 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!
  11. Fantastic!
  12. 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.
  13. sir last part is too complicated
  14. Thank you so much! Although I feel the last one is a bit crazy. But it's very excellent tutorial for someone who needs to do some analysis on data.
  15. example 14, why was 1 added to the match formula?
  16. Hi how to write an Index match function if I want to lookup the values in a single column and print them multiple columns.
  17. If I were many, many years younger I would find him and marry him! He is Awesome!
  18. NICE you will help me get the job offer.
  19. Best Match, Index explanations, video. Thank you.
  20. Hi, I have a doubt in this video, here in the sheet 7&8 we have used the sum formula to sum an array, but in your earlier video you said that sum is not programmed to handle arrays thus we used sumproduct, so how sum is working here, kindly clarify.
    thanks


Additional Information:

Visibility: 667528

Duration: 0m 0s

Rating: 2228