Index Match Using MULTIPLE CRITERIA 🙀🤯

Поделиться
HTML-код
  • Опубликовано: 19 янв 2025

Комментарии • 160

  • @EbonnCharles
    @EbonnCharles Год назад +3

    I was searching everywhere for this. Thanks so much Joseph. I salute you!!!!

  • @nameforcomments4092
    @nameforcomments4092 Год назад +7

    Thank you for this! I try to avoid multi-criteria lookups whenever possible, but the downside to that is that when they do creep up I can’t remember how to do them.
    In this case, because you’re returning a number, Sumifs would be a little shorter and easier on the RAM, but it was a great example nonetheless.
    For those curious, the Sumifs format is to specify the range the total comes from (in this case it’s just one value, which doesn’t change anything), then specify the range to look for the first criteria in, then specify the first criteria, then specify the range to look for the second criteria in, then the second criteria, third criteria range, third criteria. Here it would come out to (leaving out dollar signs for absolute references as in the example for clarity):
    =SUMIFS( E5:E13, C5:C13, B16, D5:D13, B17, B5:B13, B18)

  • @anhimals
    @anhimals 3 дня назад

    Thank you so much! I am a beginner when it comes to formulas, I usually ask my friend for the formula. This was extremely helpful and was able to help me calculated what I needed.

  • @hobknockers111
    @hobknockers111 2 года назад +3

    I just started a Data Analysis position and was flustered with matching 3 and indexing one value, THIS HELPED SO MUCH! Thank you for posting this :)

    • @jsphpalumbo
      @jsphpalumbo  2 года назад +1

      Super happy to hear it helped. And good luck on your new position!!

  • @asmrindia
    @asmrindia 9 месяцев назад +1

    Great

  • @samueloliver5598
    @samueloliver5598 2 года назад +6

    I've spent over an hour trying to get the answer to this question on different websites etc and couldn't get my sheet to work, this was super easy to follow and understand- thanks for creating it!

  • @mireillecantrell4638
    @mireillecantrell4638 2 года назад +4

    The data I need to compare: Column1 has 8 rows listing units for sale. Now each product has a sales status that spans across 5 columns: 'Contacted, Opportunity, Upside, Sold, or Lost". This tutorial is very close to what I need. I will keep testing functions. Thanks for the info. Mireille

  • @bit_Jon_Trader
    @bit_Jon_Trader 7 месяцев назад +1

    This is great, but I still get Error - Did not find value '1' in MATCH evaluation.

  • @ericsn6158
    @ericsn6158 25 дней назад

    Exactly the solution I need. ❤
    Thanks for sharing. 👍

  • @RA-rh5lb
    @RA-rh5lb 2 года назад +4

    what is the logic behind 1 where you have used in match formula?

  • @smaggies
    @smaggies Год назад

    Great thanks for you share this topic, I work from multiple sheets, with no assortment on those sheets and return what I wanted. Working to view all 50 State Income Taxes valued toward Income from all states to assume success with an Investment. Thank U :) :)

  • @stonesgoods-89
    @stonesgoods-89 Год назад

    this is much more useful than all the other videos I watched. Thanks so much!

  • @scompass
    @scompass Месяц назад

    Amazing. Does this formula also work when the look up range is not in the same sheet, i.e. a reference sheet is storing the values you are looking up?

  • @Viking12390
    @Viking12390 Год назад

    Thank you! This is exactly what i need to find! Much appreciated! Keep up the great work

  • @mikemitchell9227
    @mikemitchell9227 2 года назад

    Great video. I've used this in the past but rarely so each time I have to relearn it. This video was perfect.

  • @FiveBrogdale
    @FiveBrogdale Год назад

    Good explanation and clear example, thanks, you saved me a bunch of time!

  • @salar7735
    @salar7735 2 года назад

    Just wanted to say, your example and easy to follow video was excellent and made it much clearer than any other place I tried to look this up

  • @Viresh19
    @Viresh19 Год назад

    Excellent. Just the right solutions I was looking for. Thanks

  • @bertjanwijnhoven9272
    @bertjanwijnhoven9272 10 месяцев назад

    Hi Joseph, I am a big fan of Excel and this trick has helped me many times, only can you also do an index lookup over different sheets. I am stuggeling using an xlookup when I need results matching 2 criteria.

  • @LawrenceOoi1
    @LawrenceOoi1 2 года назад +2

    Joseph, may I know the logic behind the match search key "1", why must it be "one"? I could not find the answer in Google, hope you are kind to explain for me. Thanks.

  • @voiceofsoul7865
    @voiceofsoul7865 Год назад +1

    Very, very informative video. Thank you.

  • @kavan100
    @kavan100 Год назад

    Amazing this was such a clear and superb explanation and worked like a charm and very easy to adapt. Loved it.

  • @FranulkarNagie
    @FranulkarNagie 6 месяцев назад

    I find this very useful. Thank you for sharing.

  • @thomaschaka9233
    @thomaschaka9233 2 года назад

    Thank you so much, super helpful, exactly what I needed after spending hours looking.

  • @carloscastaneda7047
    @carloscastaneda7047 5 месяцев назад

    This is going to help me significantly when it comes to validating data

  • @0909cxc
    @0909cxc 2 года назад

    This is exactly what I was looking for. Thank you, Joseph!!

  • @johnstath9666
    @johnstath9666 5 месяцев назад

    This is great video. Do you have one where you can match multiple criteria for a result in an array of columns and rows

  • @muradayyad
    @muradayyad 2 года назад +1

    the 1 in Match Function couldnt be recognized from Excel , what should i do ?

  • @brianloftus9211
    @brianloftus9211 2 года назад

    Thank You! Very clear and concise. A wonderful description.

  • @mirosDV
    @mirosDV 5 месяцев назад

    I like the way you explained the "1". In other words, return a 1 if all these conditions here are true, or else false. Thank you!

  • @kanokratjantra
    @kanokratjantra 11 месяцев назад

    That's really helpful formula I'm looking for!!! Thanks much ❤

  • @timonpumbaa715
    @timonpumbaa715 10 месяцев назад

    Thank you for the Knowledge, suggestions to put more videos based on excel sorting techniques, and index is powerful, and the best way to sort data on criteria, sorry Vlookup and XLookup you guys are interesting but index is surprising, if and only if know how to use it to the requirement; Great Job Sir. Regards.

  • @OndosSaragih
    @OndosSaragih 9 месяцев назад

    Thank you, thank you, thank you! You are incredibly helpful!

  • @hn_my9361
    @hn_my9361 Год назад +1

    i manage to create formula on fixed value data...however when it comes to formulated data it return #N/A instead of value...how do i fix this

    • @merrickhemmings
      @merrickhemmings 8 месяцев назад

      use xlookup in the cells that are formulated. So when they change so does your index match cell

  • @cenifh
    @cenifh 3 месяца назад

    Marry me!! been looking for this for like a week!! finally it worked!

  • @stephenlugaimukamu9187
    @stephenlugaimukamu9187 Год назад +1

    This is the best solution, thanks alot

  • @winabraham5619
    @winabraham5619 2 года назад +1

    Can u help me out with the logic of 1 being used for look-up value?

  • @alannaearl6633
    @alannaearl6633 2 года назад +1

    Can this be achieved with Multiple criteria using xlookup?

  • @ladev91
    @ladev91 Год назад

    Hello, do you know how to sum all of the values if there are multiple values that fit the criteria?

  • @АннаКендзерська
    @АннаКендзерська 8 месяцев назад

    i see message that function MATCH couldn't find value 1. Any ideas why this happened, recommendations? Will appreciate a lot!

  • @AnkitKumar-kt3sy
    @AnkitKumar-kt3sy 29 дней назад

    Best use yet of index match formula

  • @richardgordon
    @richardgordon 2 года назад

    Thanks a lot. Your video solved a big headache for me.

  • @Oyongr
    @Oyongr Год назад

    can you please explain what is the MATCH(1? where did you get the 1 value?

    • @jsphpalumbo
      @jsphpalumbo  Год назад +1

      Hey Richard, I explain the logic behind matching to 1 in this video. XLOOKUP from BASIC to ADVANCED using MULTIPLE CRITERIA for #Excel and #googlesheets
      ruclips.net/video/RGEv4c9G8Zc/видео.html
      Check it out!

  • @DanhNyiahm
    @DanhNyiahm 11 месяцев назад

    Hi Joseph, hope you are well. You have a very nice presentation. I have something on excel which I would like you to do. Do you have any website that I can reach out for your contact? Thanks

  • @Always_ponder
    @Always_ponder Год назад

    Fantastically explained !

  • @naseem.banu.z
    @naseem.banu.z Год назад

    Thanks for this, but im getting this error, "Did not find value '1' in MATCH evaluation." how to go about it ? and aslo im trying this in Gsheets.

  • @DannyFish-o1l
    @DannyFish-o1l Год назад

    Hello Josheph, this works great if you're pulling from the same sheet, but it don't want to work if you're matching criteria from a 2nd sheet. can you give some pointer on that.

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      It should work the same if you're referencing the data on the 2nd sheet properly, like (Sheet2:A1:A10 = '1'). Have you tried that?

  • @edwardmusshafen5675
    @edwardmusshafen5675 Год назад

    Would index match be the right way to match claims data for pharmacy claims to find a certain payment or fee using 2 different worksheets? I have a worksheet with pharmacy claims data where I need to match the same claims data on an other worksheet that shows a fee that is applied. I need to match the Rx# and date filled from 2 data sets, but then one of those data sets shows a fee that is paid. I need that fee amount added to the claims data spreadsheet so I can accurately calculate the Net price and for the claim.

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      @edwardmusshafen5675 Index + Match would definitely be one way of doing it between 2 tabs in the same workbook. If you're trying to do it between two workbooks (2 different files) you will probably run into limitations. I'd also recommend trying =XLOOKUP which is the improved method of INDEX + MATCH. I have a video on how to that on my channel.

  • @johnw9269
    @johnw9269 Год назад

    Hi, your video is really informative and practical. But what if I have another scenario whereby my match involves exact match and approximate match? How to perform both match type together under 1 index and match function. Are we able to achieve that? I tried before but I couldn't get it as I want to be able to use this formula in any excel version.

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      Hi, sorry for the late reply. Unfortunately, there isn't one single function that will match with both exact and approximate values, but if you need to match within a range of values (e.g. >= 100) then I would use the FILTER function with multiple conditionals to accomplish that
      =FILTER(A:A,B:B=100,B:B>100)

  • @MAJIN_MAGIC
    @MAJIN_MAGIC Год назад

    Great video can you recommend what to do if you have multplie matches?

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      Yes, XLOOKUP is designed to find a specific match, but if you want to find all the matches for a set of criteria I would use the =FILTER function with multiple sets of criteria.

  • @chandrimapramanick1111
    @chandrimapramanick1111 Год назад

    Amazing explanation!

  • @YepKim
    @YepKim Год назад

    This is what I exactly need. Thanks so much!😀

  • @lokedistor5860
    @lokedistor5860 Год назад

    where would you put an IFERROR FUNCTION???

  • @kimberlysuriner9750
    @kimberlysuriner9750 Год назад

    love this video; but when trying to do it; I keep getting the spill error- any ideas on how to fix that?

  • @alezon97
    @alezon97 Год назад

    It works great! But, my table uses an average and person a/b/c/d has a tendency to have multiple scores/data for a specified week in a month. how do compute that? i'd appreciate it if you can respond to this. thank you!

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      Hi there, would love to help out, but I'm afraid I don't understand the table you're working with. Can you provide some more information, or reach out to me directly?

  • @habibansubuga2731
    @habibansubuga2731 Год назад

    This was very helpful, thank you!

  • @GV-gn3mj
    @GV-gn3mj 2 года назад +1

    hi, thank you. why 1 after match? and why multiplication,also what if I had needed the last number $2000? would I have to changed 1 and instead put 4 in the formula?

    • @galerion
      @galerion 2 года назад +5

      True=1 False=0, Match wants to = that 1 so where he has (C16=C5:C13) match will look for the first instance of that being true which means it has a value of 1. It will then do that for all the ranges until (C16=C5:C13)*(C17=D5:D13)*(C18=B5:B13) results in (C16=1)*(C17=1)*(C18=1)=1 across a row or if there is no row that results in 1; only 0 it will give an N/A error.
      * is "and" and + is "or".
      To get $2000 C17 needs to be changed to March, if you changed the 1 to 4 match wouldn't find 4 as the sums he's used can only = 1 or 0.

    • @GV-gn3mj
      @GV-gn3mj 2 года назад

      @@galerion I appreciate it. thank you!

  • @evrys
    @evrys 2 года назад

    Very good use case! And so easy to remember. Thanks

  • @qutaibaabusmaiha7413
    @qutaibaabusmaiha7413 2 года назад +2

    it Worked BUT, you pressed ENTER to apply the formula and it didnt work with me and i had to press CTRL+SHIFT+ENTER (Array Fromula) to work it out, i dont understand Actually can you explain?

    • @hari.potter
      @hari.potter Год назад

      The same happend with me, could you get any answer for this problem?

  • @hari.potter
    @hari.potter Год назад

    Why i had to enter Ctl+Shift+Enter to reach to the same result which you could do only with Enter, any reason?

  • @HippieMosquitoQuatro
    @HippieMosquitoQuatro Год назад

    how do you do this to find multiple matches? so say John Baker, from Alpha Solutions, during the month of May: had more than 1 invoice? how do you return more than 1 match?

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      It depends how many matches there are in the table, but one method is to do a top-to-bottom match, and then do a bottom-to-top match. This might also be a case where VLOOKUP is a better option than Index + Match.

  • @tanichcka
    @tanichcka Год назад

    Thank you for this video. This is the exact function I need for my project. Unfortunately I am getting over and over an #ERROR despite following you step by step and checking up the syntaxes multiple times. Tried also as array formula, but it didn't work. May you please direct me where the problem could be? Thanks :)

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      Hi there, sorry you keep getting errors. My guess is it's something to do with the values in your spreadsheet. When putting together your INDEX(MATCH) function try wrapping your lookup values in VALUE() to see if that fixes it.

  • @money_excel
    @money_excel Год назад

    Brilliantly explained!!!

  • @mariedjusthine
    @mariedjusthine 4 месяца назад

    Thank you! U saved my day. ❤

  • @geethaganesh4174
    @geethaganesh4174 Год назад

    Instead of the first match value, if I want to retrieve the sum of all John bakers with month May, company alpha( having multiple entries with same criteria), how do i change the formula

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      use the =FILTER function for that use case, INDEX(MATCH()) is not a good fit

  • @maturebeautywithgloria
    @maturebeautywithgloria Год назад

    Exactly what I’m looking for thank you

  • @Pins33ker
    @Pins33ker Год назад

    You helped me out because this example is much more closer to my problem I am trying to solve. For me I am trying to match a name that comes up more than once, but I want to find the highest number connected to that name. Would I need to also use the "Max" command for that with Match and Index ?

    • @jsphpalumbo
      @jsphpalumbo  Год назад +1

      @rarpcgaming No, I would use =SORT function to grab the largest value. For example, using XLOOKUP (which is much more flexible compared to INDEX + MATCH) my formula would look like this
      =XLOOKUP("Joseph", B4:B7, SORT(B4:C7,2, FALSE))
      =XLOOKUP([search key], [lookup range], [return range])
      This will lookup the value you want, but always return the greatest value in the lookup range.
      Let me know if you want me to make a video on how to do this.

  • @sass1330
    @sass1330 11 месяцев назад

    What if i have a value and i want to match it with an exact value from another excel and if not to the next greater value in a row???

    • @sass1330
      @sass1330 11 месяцев назад

      The first value I have is a basic salary and I want to match it with the value from the payscale I have in the other excel

  • @jonsmith1472
    @jonsmith1472 2 года назад

    Does this work similarly to COUNTIF function?

  • @cristianmojica3748
    @cristianmojica3748 2 года назад

    very easy and straight to the point. Thanks

  • @ekelly858
    @ekelly858 7 месяцев назад

    Excellent 😊

  • @linz6878
    @linz6878 Год назад

    Omg this exactly what i need but What does this look like if the data is on a separate tab? 😅

  • @SupportManager-e6x
    @SupportManager-e6x 4 месяца назад

    Way helpful thank you!

  • @alfdray2850
    @alfdray2850 2 года назад

    Wow. Thanks a million. this is the greatest.

  • @baderarfaj
    @baderarfaj Год назад

    I like it. Wonderfull thanks a lot

  • @ITTEC.
    @ITTEC. Год назад

    dear sir but no answer ! in this condition

  • @RajKumar-hv8kq
    @RajKumar-hv8kq 9 месяцев назад

    How to use these between 2 Google sheets.

  • @holyfunny7536
    @holyfunny7536 2 года назад +1

    I tried it and used the very same data as guy using but it gives an #VALUE!, What is problem?

  • @mauricelee2219
    @mauricelee2219 Год назад

    Hi, good day, need favor and assistance, is there a way to do this in vba code.

  • @GOATMENTATOR
    @GOATMENTATOR 2 года назад

    I have one table with many many records where each record has a category(meaning multiple records can have the same category). And separate table where I have specific values for each category. I want to add the values in first table from second table in a new column based on category. Can someone help please I don't know what functions to use? It will take days to do it manually..

  • @mouazosama9891
    @mouazosama9891 2 года назад

    i think you can use filter function to do it , it is less complex

  • @Braydaft
    @Braydaft Год назад

    I have the names "Antonio" and "AJ" in my list, and I'm trying to find the invoice for Antonio with the company "Push"; it's pulling up AJ's invoice, even though his match is AJ and Pull. None of the criteria is met, yet it is matching with him instead of Antonio..... any help would be nice.....

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      Hey Baydaft, thanks for the comment. Can you share your formula so I can take a look at it?

    • @Braydaft
      @Braydaft Год назад

      @@jsphpalumbo Hey, thanks for replying! I ended up fixing it, apparently changing my Range from E:E to E2:E worked; I guess it didn’t like having the header included?

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      @@Braydaft Great catch!! Yeah, sometimes a header can cause problems when defining ranges. So it's a good point when troubleshooting to try to exclude headers. 🤜🏼💥🤛🏼

  • @richadsilva6498
    @richadsilva6498 2 года назад

    Very helpful thank you!

  • @DanielChacon-uk9qm
    @DanielChacon-uk9qm 8 месяцев назад

    Thank you for this!

  • @bigbrother1211
    @bigbrother1211 2 года назад

    brilliant work, thanks!

  • @maturebeautywithgloria
    @maturebeautywithgloria Год назад

    I can’t get this to work?

  • @operationbroomstick
    @operationbroomstick 2 года назад

    Very useful thank you

  • @amazingytvideos5214
    @amazingytvideos5214 Год назад

    thank you for this boss!

  • @mudmud357
    @mudmud357 Год назад

    What doe’s Number 1 do actually?

    • @jsphpalumbo
      @jsphpalumbo  Год назад

      OK, I explain this in detail in another video, but the short version is that you're searching for TRUE (represented as 1) or FALSE (represented as 0) multiplied together. So if one part of your query is true (1) and another is false (0) the answer will be 0. The expression will only return 1 if the multiple search criteria are returned true across all columns. That's why you're actually looking for 1.

  • @juliand1053
    @juliand1053 2 года назад

    EXCELlent! Thank You!

  • @snehaagrawal3036
    @snehaagrawal3036 2 года назад +2

    It is not working. It's throwing error.

  • @abdalbastsalm5801
    @abdalbastsalm5801 9 месяцев назад

    very good

  • @MickaelNECHACHBY
    @MickaelNECHACHBY Год назад

    Thanks for this!

  • @ВикторКарпенко-й2г

    Thank you so much!

  • @Gert-JanMStoop
    @Gert-JanMStoop 2 года назад

    Yes hou have been very helphull. Thank you.

  • @Ibaikard
    @Ibaikard 2 месяца назад

    you're a godsend

  • @prabathwerahera1961
    @prabathwerahera1961 4 месяца назад

    Thank you. you saved me

  • @girishyanamala9361
    @girishyanamala9361 Год назад

    🎉🎉🎉super

  • @abhishekkuradia898
    @abhishekkuradia898 6 месяцев назад

    Thank you so much ❤

  • @jamesmoore4766
    @jamesmoore4766 2 года назад

    ok please type back has this is not wok for me ...follow the info and get an N/A .....help and thanks