Index Match Using MULTIPLE CRITERIA 🙀🤯 

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

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

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

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

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

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

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)

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

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

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

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!

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

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

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

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

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

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

Exactly what I’m looking for thank you

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

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

I find this very useful. Thank you for sharing.

Very, very informative video. Thank you.

Thank you for this!

Brilliantly explained!!!

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

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

very easy and straight to the point. Thanks

Wow. Thanks a million. this is the greatest.

EXCELlent! Thank You!

Very helpful thank you!

This was very helpful, thank you!

Great

Amazing explanation!

Fantastically explained !

Thanks for this!

Excellent 😊

thank you for this boss!

brilliant work, thanks!

Thank you so much ❤

perfect, thanks

Very useful thank you

GOOD VIDEO

I like it. Wonderfull thanks a lot

Yes hou have been very helphull. Thank you.

very good

🎉🎉🎉super

Nice!

Thank you so much!

This is the best solution, thanks alot

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

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.

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

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

mashallah sir

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.

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

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

genius!

thank you...

Does this work similarly to COUNTIF function?

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

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.

🤩

🤝

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

Can this be achieved with Multiple criteria using xlookup?

wizard!

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 ?

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

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

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?

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.

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.

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

where would you put an IFERROR FUNCTION???

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

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!

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.

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

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

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

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.

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?

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

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

Ctrl+shift+enter for excel

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

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

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

How to use these between 2 Google sheets.

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?

I can’t get this to work?

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

thanks...more understandable than listening to an indian accent

use Filter - much more simple

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

It is not working. It's throwing error.

dear sir but no answer ! in this condition

What doe’s Number 1 do actually?

the formula worked on me but it causes my ms excel to lag