Index and match functions

[Firecrest]

Hello,

I am new to this forum. I'm also a pretty much a beginner in Excel, so I'd appreciate anyone who may be able to help me on the following:

The attached spreadsheet provides a dummy list of houses sold at an auction, and the value that each was sold. Also each house is allocated a type indentity of either "Gold" or "Silver". From this, I need to extract the following info:

The address of the house's identity (ie: Gold or Silver) sold at the minimum and maximum value. The "index and match" formula in G5 and G6 in the spreadsheet provided only searches for the first address of the respective min. and max. value of the houses listed, not the address of the first house by identity type.Is there a way of doing this without having to break-up the list on a seperate worksheet by house type? Hopefully the spreadsheet provided will give you an idea of what I'm trying to achieve.

As an aside, you'll see from the spreadsheet that I am able to extract the value of the minimum and maximum price of the house sold by house type (using a combined array max/min. and if formula) listed.

Thank you



Peter

[NBVC]

Try:

=INDEX($A$4:$A$12,MATCH(1,($B$4:$B$12=$F$3)*($C$4:$C$12=F5),0))

confirmed with CTRL+SHIFT+ENTER and copied down

[Firecrest]

Thanks for the swift response. Works brilliantly. You've saved me a lot of headache.

Thanks


Peter

[Pepe Le Mokko]

Yes, NBVC is much better than aspirin