Get nth match with INDEX/MATCH with multiple criteria

**MattRoux** · 07-31-2019, 04:00 AM

Hi,

I'm trying to add multiple criteria to the formula I found on a website.
{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

For their example, this formula translates to {=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6))}

This formula works for me, but now I would like to add 2 more criteria. One that the number has to be bigger than or equal to a threshold and one that the number has to be smaller than or equal to another threshold.

The website says I have to look at boolean logic and gives a link to this function {=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Which translates to this formula in their example {=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}
but the function on there doesn't let me get the nth match, only the first.

I'm looking for a way to combine both functions, so I can have multiple criteria, but still get the nth outcome.

Kind regards,

Matt

**AliGW** · 07-31-2019, 04:03 AM

You might want to have a quick look at this (it suggests using AGGREGATE): https://stackoverflow.com/questions/...function-excel

**MattRoux** · 07-31-2019, 07:21 AM

Hi Ali,

Thanks a lot for your reply!
I got the numbers to work, however, I also have some cells that I want to display text in for the cell that matches the criteria and those cells get a #NUM! error.
Do you know how to solve this by any chance?

Kind regards,

Matt

**AliGW** · 07-31-2019, 07:24 AM

Can you share a sample workbook so that we can help resolve this?

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**MattRoux** · 07-31-2019, 07:38 AM

I hope I did it right, it should be attached now.

What I also noticed is that the results it gives me are largest to smallest for every single column in there, so the rows are not linked to each other anymore.

An example would be that the date 25-7-2019 should not be linked to the EV of 159 million. The data can be found in the other tab.

Kind regards,

Matt

**MattRoux** · 08-05-2019, 05:03 AM

Dear Ali,

Did you get the chance to have a look at the sample workbook?

Kind regards,

Matt

**AliGW** · 08-05-2019, 05:05 AM

No - I didn't see it. I'll have a look now and see if I can help.

**MattRoux** · 08-05-2019, 05:05 AM

Thanks a lot, I really appreciate your help!

**AliGW** · 08-05-2019, 05:07 AM

Here's the problem:

Excel 2016 (Windows) 32 bit

	A
10	5
11	4
12	3
13	2
14	1
15	6
16	7
17	8
18	9
19	10

Sheet: Sheet1

The formula needs these values to be in order. Yours go from 1 to 10 in the middle - this won't work.

**MattRoux** · 08-05-2019, 05:22 AM

I did that, but it won't change the answers.
I found a mistake on my side as well, and corrected that, so I will put the updated file in the attachments.

What I see happen is that for each category I'm looking at, it chooses the lowest value individually, instead of linking a full row and showing the related info.
The second thing I'm still struggling with is that the function as it is right now only shows number, but not text, and I would like to see the related text of the data file as well if possible.

For example, if you look at row 10, it gives the date 3-5-2019 and shows an ev/ebitda of 4,81 and an ev of 2.470.000. But if we look at the data tab, the dat 3-5-2019 should have an ev/ebitda of 14,48 and an ev of 7.500.000.

Kind regards,

Matt

**AliGW** · 08-05-2019, 05:26 AM

Forget the formulae for a moment - explain IN WORDS what the purpose of your summary sheet is.

We are dealing with a matrix of incorrect answers and errors at the moment - ideally you should mock up manually your EXPECTED RESULTS and explain why you expect them.

**MattRoux** · 08-05-2019, 05:43 AM

Alright, you can find the expected output and the explanation in words in the file in the attachments.

I hope this makes things more clear.

And again, I really appreciate the effort you put into helping me, thanks so much!

Kind regards,

Matt

**AliGW** · 08-05-2019, 05:49 AM

Could dates ever be repeated in the source table? I'm guessing they could. If so, what is the second level identifier for each row?

I am ignoring your explanation in the workbook because you are still fixating on an anticipated formula instead of explaining simply what the objective is. I only have so many hours a day to spend on helping here, so I like to cut to the chase.

Is the objective simply to display the data in date order?

**AliGW** · 08-05-2019, 05:52 AM

By the way, please update your user profile to tell us WHICH VERSION of Excel you are using.

**MattRoux** · 08-05-2019, 06:37 AM

The objective is to display the data in the order of the data file.
So if I would change the order in the data file, the order of the output would change as well.

The date repetition shouldn't be a problem, because we are looking for the nth match in the data file match starting from row 1 and going down.

**AliGW** · 08-05-2019, 06:43 AM

OK - I think I get it now.

**AliGW** · 08-05-2019, 06:46 AM

I think, from what you have said, this is all you need:

=INDEX(Data!A$2:A$10,Sheet1!$A26)

EDIT: Or not ...

The penny has just dropped. Why don't you just use a slicer on the datasheet?

**MattRoux** · 08-05-2019, 06:51 AM

That's already a big step in the right direction, thanks. The only thing I'm looking for after this is how to include the 'filter' of the sub industry (B6) and the deal size range (H5;J5).

**MattRoux** · 08-05-2019, 07:05 AM

I don't use slicers, as in the original file the data will be in a different file, which will not be accessible for other people using it. This is just a small summary version, the real file will have 5000+ rows with data ranging over 10 years and about 80 different sub industries which can be selected with a dropdown menu (B6).

That's the reason I would like to have the filters in the formula.

EDIT:
To give some more context, the aim is to make the model as easy as possible to use for people who haven't worked on making it, so they literally just need to give the sub industry they are interested in and the range of deal size ev

**AliGW** · 08-05-2019, 07:39 AM

OK - this seems to me the ideal scenario for PowerQuery to deal with, but I have run out of time just now. If some other PQ expert like Olly doesn't step in, I'll try and have a look later on, but I have to go out for a while now.

**MattRoux** · 08-05-2019, 07:43 AM

Alright, thanks!

**jason.b75** · 08-05-2019, 01:01 PM

I'll leave PQ to Ali, but I've updated your sample file with a formula setup that I think is doing what you're asking for.

I've inserted a new column A to hold a variation of the aggregate formula which identifies the rows of interest in the data sheet, then used index to pull the data into the table.

I've made it as efficient as possible, but it will still be slow with 5k rows of data to process, so probably still a good idea to look into PQ.

**MattRoux** · 08-06-2019, 03:27 AM

Edit: Misunderstood

**MattRoux** · 08-06-2019, 09:47 AM

Hi Jason,

I misunderstood/interpreted your response, this does turn out to be what I needed.

Thank you so much, and sorry for the initial misunderstanding!

Kind regards,

Matt

**jason.b75** · 08-06-2019, 09:57 AM

That is exacty what it does, as far as I can see, the results of the formula are a 100% match for your expected results.

Formula:

Please Login or Register  to view this content.

Each of the 3 sections in bold forms a boolean logic test, the first checks that the GICS SubInd name is equal to C6, the second and third check that EV is greater than I5 and less than K5.

If the results are wrong with different criteria then please provide an example of this.

**jason.b75** · 08-06-2019, 10:00 AM

Ignore that, your last post and edit came through after I refreshed.

I still think that PQ will be a better option for what you need, if it can do what is required. Hopefully someone else will be able to assist there.

I'll give it a go later, but I have very little PQ experience so even if it is possible it might not be something that I can do.

**MattRoux** · 08-06-2019, 10:16 AM

The only thing that I had to do was -1 in the last part of the Index function (so e.g. $A10-1 instead of $A10), because for some reason in my main file (different from the small file you worked on) it gave the answer below the row given by your formula.
Just as a quick check with you, does this sound right to you?

It seems to work in my main file after the -1 adjustment .

EDIT:
The index function in the different categories, so e.g. for date or target. Not the new formula in column A you came up with.

**AliGW** · 08-06-2019, 10:20 AM

Is this sill ongoing or are you satisfied with the solution offered here?

**MattRoux** · 08-06-2019, 10:21 AM

I'm satisfied, thanks for your time. Really appreciate the effort and time you put it, as I know I wasn't the easiest person to deal with

**jason.b75** · 08-06-2019, 10:33 AM

Did you use entire columns with Index, as I did with the formula in the sample file? If you used a limited range starting from row 2, then that would explain it.

**AliGW** · 08-06-2019, 10:59 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Re: Get nth match with INDEX/MATCH with multiple criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX/MATCH with Multiple MATCH criteria ?

[SOLVED] Index Match using multiple criteria to match to

Bookmarks

Bookmarks

Posting Permissions