Finding product prices with criteria.

[Gordian27]

I need to find a product and a brand at the lowest and highest price only for Lipstick segemnt.
task3.xlsx

[sandy666]

something like this ?

Mark	Segment	Min	Max
Brand A	Lipstick	8.9	25.63
Brand B	Lipstick	6.6	25.33
Brand C	Lipstick	6.4	25.42

[sandy666]

update your profile about Excel Product and Version, eg XL2021 or XL365 v.2301 and so on

GoogleTranslate:
zaktualizuj swój profil o produkt i wersję programu Excel, np. XL2021 lub XL365 v.2301 i tak dalej

[Gregb11]

Of course you will want to reference a cell that has the word "Lipstick" in it, but try this:

=INDEX(E2:E186,MATCH("Lipstick"&MAXIFS($H$2:$H$186,$G$2:$G$186,"Lipstick"),$G$2:$G$186&$H$2:$H$186,0))

And as another option if you have 365 or 2021:

=FILTER(E2:F186,(G2:G186="Lipstick")*($H$2:$H$186=MAXIFS($H$2:$H$186,$G$2:$G$186,"Lipstick")))


(of course just switch out MINIFS for MAXIFS to find the least)

[Croweater]

This should work regardless of which version of excel you are using;

In Tab Table 1,2
in cell D13

=INDEX(Data!$E$2:$E$9999,MATCH(MIN(IF(Data!$G$2:$G$9999="Lipstick",Data!$H$2:$H$9999)),Data!H$2:$H$9999,0))

in cell E13

=INDEX(Data!$E$2:$E$9999,MATCH(MIN(IF(Data!$G$2:$G$9999="Lipstick",Data!$H$2:$H$9999)),Data!H$2:$H$9999,0))

in cell F13

=MIN(IF(Data!$G$2:$G$9999="Lipstick",Data!$H$2:$H$9999))

Drag each one of these formula down and change MIN to MAX

Note these are all array formulae so must be entered with a CSE (ie. copy the formula into the cell then Press Enter whilst holding the Ctrl and Shift buttons down simultaneously)

[kvsrinivasamurthy]

Pl see file.
For lowest cost in L2

Please Login or Register  to view this content.

For highest cost in N2

Please Login or Register  to view this content.

[Gordian27]

Thank you all, that's correct

[sandy666]

Thank you, that's correct

to whom is your post ?

[Gordian27]

to whom is your post ?

To Croweater for genius formula.
Of course, I also thank the others for your interest in the problem, sank you.

[sandy666]

next time use @username

did you read post#3 ?

---

if the problem is solved, hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

[Gordian27]

Yes, "update your profile", thank you.

[Croweater]

To Croweater for genius formula.

Thanks for the feedback/rep.