Excel Formula

Heedtheadvice

Are you sure that works @double_dutchy ?

double_dutchy

Heedtheadvice said:

Are you sure that works @double_dutchy ?

Yes, absolutely, did you try it?

Heedtheadvice

I did not need to.

Granted it may work on some data circumstances such as

item	price		formula
Item A	£1.00		4
Item A	£2.00
Item B	£3.00
Item A	£4.00
Item B	£5.00
Item C	£6.00

(Price column formated as currency formula used as supplied by Double_dutchy)

But it fails in other cases where the data prices are not on ascending order. As below

item	price		formula
Item A	£1.00		4
Item A	£10.00
Item B	£3.00
Item A	£4.00
Item B	£5.00
Item C	£6.00

 Answer given by the formula is still 4 but it should be 10....

[ EDIT: see posts below as the assumption here is that max price is required not just the item furthest down the column....]

DullGreyGuy

Heedtheadvice said:

I did not need to.

Granted it may work on some data circumstances such as

item	price		formula
Item A	£1.00		4
Item A	£2.00
Item B	£3.00
Item A	£4.00
Item B	£5.00
Item C	£6.00

(Price column formated as currency formula used as supplied by Double_dutchy)

But it fails in other cases where the data prices are not on ascending order. As below

item	price		formula
Item A	£1.00		4
Item A	£10.00
Item B	£3.00
Item A	£4.00
Item B	£5.00
Item C	£6.00

 Answer given by the formula is still 4 but it should be 10....

Answer should be 4... the requirement from the OP was simply to give the "price" of the last entry for a given item not the max value for a given value.

In the real world you'd really expect some form of date stamp or something else in the table that defines the order rather than arbitrary sort. 

Heedtheadvice

True that is what was written and to that extent the formula does work. So apology to @double_dutchy !

However I assumed ( perhaps incorrectly) given that all the prices were in ascending order that the max was required not just the lowest in the column. As you say date/time stamping is really required in that case as it is easy to mix up the order unintentionally either at data entry or with a data sort.

Perhaps the OP will clarify the need if not for their own benefit but for others who might be learning from this thread?

double_dutchy

Heedtheadvice said:

So apology to @double_dutchy !

No problem!

I was, indeed, assuming that the requirement was to get the last row where the criterion was satisfied, as DullGreyGuy says. My suggested LOOKUP formula will do that, as will the XLOOKUP formula suggested. If the requirement was to get the largest value where the criterion is satisfied then in the latest versions of Excel you could use MAXIFS function, i.e.

=MAXIFS(B:B,A:A,"Item A")

Heedtheadvice

A great addition to up to date Excel versions. There are are lot of new ones now but I admit I am old school so glad others can  inform us about.

Grey_Critic

For thiose with a Kindle you can buy/download  ***how to use microsoft excel *** I managed to find one for Free and very handy at times.