• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match with a Min [SOLVED]

R

rjacmuto32

Member
I've learned a lot about the Index Match formula on this site from articles and some questions. I'm stuck doing something like this below and wonder if it can be done.


I have a data tab with Column A is City, Column B is Type, Column C is a price for Group 1, Column D is a price for group 2.


I want to have a summary tab where the data will change based on conditions in a drop down people can select. On my summary tab, those are

B1 = Type (say One Bedroom, for this example), B2 = Group (Group 2 in this example).


Then below that I want to list all my city names.


The formula's I want to do are a Min, Max and Avg for each city based on the type and Group. How can that be done?


I've tried a index match to match the Group to tell it to use Column C or D in the data for the numebrs but no luck there.


Thanks,

Robert
 
See if this works.

[pre]
Code: 
City	Type	Group A	Group B
Paris	1 Bedroom	1	67
Rome	1 Bedroom	2	34
Rome	1 Bedroom	45	23
NYC	1 Bedroom	12	78
NYC	2 Bedroom	56	56
NYC	2 Bedroom	23	34
NYC	1 Bedroom	78	23
Paris	1 Bedroom	23	45
Rome	1 Bedroom	23	67
[/pre]

Looking for:


'

Type 1 Bedroom

Group A


Min Max Avg

Paris ? ? ?

Rome ? ? ?

NYC ? ? ?

'


I tried to upload a workbook but didn't work. The ? would change above depending on the items selected just above in type and group. In this one above what is the Min for Paris, 1 Bedroom using the Group A price column.
 
Please see this one hope it resolves the issue:


https://dl.dropboxusercontent.com/u/60644346/rjacmuto32.xlsx


Regards and Good Night :)


Faseeh
 
That is a start. It's missing another piece using Column B though for Type. I see the formula in cell H8 that you have. But what if it's the Min of both Paris, 1 Bedroom and Group B. Is there a MINIFS formula to use? I see this part F($A$3:$A$11=$G8 where is't looking in Column A to match it to G8, but it also needs to look at colum B and match that too? I know I can insert a column and just use a concate formula to combine both columns A and B but wondering if it's possible to do the MIN IFS in there or something like that.
 
That works perfectly. In some trials I was doing before, I didn't have the MIN in the front of the formula but rather in the middle. Thanks for the help on this. I understand the formula now seeing it and reading it.
 
You must log in or register to reply here.
Back
Top