INDEX MATCH with MAX and MIN Combined

[Paul.Thompson]

I have another strange one.

The attached should better demonstrate what I probably wont be able to articulate in words now. The data in the attached is sample data.

I need to look across a table of data and find where the columns for resource1, 2 and 3 have results in (there could be up to 24 rows and 36 columns that have results in. I have put a little helper column in to do this (count if).
I then need sum the results and then determine which one is cheapest. (I have put a little helper column in to do this (sum if).
Once i have done that, pull the results from the table and put into another cell (the cells in red on the attached).

The descending order of results would be
3 matches (cheapest)
3 matches more expensive +
2 matches cheapest
2 matches more expensive+.....
1 match cheapest
1 match more expensive +....

Any help with a formulae for this would be very much appreciated.

[Paul.Thompson]

Slightly amended file as there were a few more areas highlighted in red that I need populating. Many thanks whoever can help.

[BadlySpelledBuoy]

OK, took me a while to work out the requirement, but I think I'm there!

I've added formulas to all the red cells. Some are just sums and therefore self explanatory. Others are a little more complicated.

The below is the formula from M31. The rest are build around the same principle, but with ranges amended to suit.

Please Login or Register  to view this content.

This formula is finding instances of 3 in your helper column then finding the minimum value against a 3. It then uses that to lookup the rest of the values using an INDEX/MATCH. For the subsequent sections if looks for the minimum value against 2 and then against 1.

It is an array formula and therefore needs to be confirmed with Ctrl+Shift+Enter.

Hope it makes some sense as well as does what you need.

Let me know.

BSB

[Paul.Thompson]

Hi - You have helped me out twice in two days so thank you and serious hats off to you. I understand the logic so thank you. I just need to adapt it slightly as there may be 3 or 2 instances where "3" is featured which is where I keep falling down .

[BadlySpelledBuoy]

So if there are multiple instances of 3 then those should be shown rather than a 3, a 2 and a 1?

BSB

[Paul.Thompson]

Yes - sorry I tried to explain above in the original post

"The descending order of results would be
3 matches (cheapest)
3 matches more expensive +….. and only when this is exhausted then
2 matches cheapest
2 matches more expensive+..... and only when this is exhausted then
1 match cheapest
1 match more expensive +....

also there may be a value in "D" if the user selected hours instead of days - I have tried to modify your formulae to accommodate but that does not work:

=IF(G23>0,INDEX($F$6:$F$19,MATCH(MIN(IF(($Q$6:$Q$19=3),$R$6:$R$19)),$R$6:$R$19,0)),INDEX($D$6:$D$19,MATCH(MIN(IF(($Q$6:$Q$19=3),$R$6:$R$19)),$R$6:$R$19,0)))

I really appreciate your help - I think you know that !

[BadlySpelledBuoy]

OK, gotcha now. I'll have to have a bit of a think on the best way round that though. I'll hopefully come back with a solution soon (need to pop out for a while).

Throwing a whole other element into it with the days/hours thing... Can I just check if the user has selected hours then columns D, H & L will have figures and columns F, J & N will all show zeros, and the other way round if days is selected?
What I'm getting at is there will be no times when some show hourly figures and some show daily??

BSB

[Paul.Thompson]

that's exactly right I will deal with the weekly item if a user chooses thin in the user data entry part by (x) the daily by five so dont need to cater for that

[BadlySpelledBuoy]

I've added a couple more helpers to this version. The first takes the count number from col Q and a ranked value from R based on the count number and turns them into a decimal number.
The second ranks those decimal numbers. I've then changed the array formulas that were doing the lookups before for straight forward INDEX/MATCHes to bring back the results bases on this second rank helper.

If there are 3 "3"s it will show those, if there are 2 "3"s and a "2" it will show those. Hopefully that's what you meant!

You will be able to use your logic from earlier to check if G23 >0 and if so look in hourly columns rather than daily columns.

BSB

EDIT:
This in S6 and copied down would be a nicer formula (same result though)

Please Login or Register  to view this content.

[Paul.Thompson]

OMG - THANK YOU.... A MILLION THANK YOUs!!!!! you have saved my bacon. I hope you have a fantastic weekend

[Paul.Thompson]

If I could give you another appreciation I would - sorry!

[BadlySpelledBuoy]

Happy to help mate

BSB