Sorry if this request seems rather basic to most or the description of what I want vague, I am rather new to using excel for manipulating my data.

Essentially I want to do something similar to a VLOOKUP (maybe not but this is the function I know).


Lookup a value within a range of data but then I want the formula to return the greatest value out of the return cells.
I would also like to find the lowest value but as a separate formula.

E.g.
You have a sheet with "Part Numbers" in Column A, "Quantity" in Column B.
I want to be able to lookup a part number and return the highest quantity of that part in the data range.
In a second formula I want to return the lowest.
Assume a part number will appear within the data range multiple times but with different quantities.

Thank you in advance for any and all help

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formulas:

=MAX(IF(A1:A100=C1,B1:B100))

=MIN(IF(A1:A100=C1,B1:B100))

where C1 contains the part "number" entered as the same type as the values in column A (text or numeric).

Thanks for the quick response.

I gave it a go, it looks in concept like what I need but it has the issue of returning the greatest value in B1:B100 regardless if that particular value is associated with the value in C1. It seems to be just checking if A1:A100 has the C1 value but not using it again for the filtering.

Perhaps post a sample sheet? ( Go Advanced - Manage attachments)
Did you commit the formula correctly?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Never mind, found the issue, thank you for your time and help!
Now is it possible to add another column of data to the formula as an added step for filtering the results?

E.g.
In the provided spreadsheet could I also have the formula filter for only those that have TRUE in column C.

Attached Files

FormulaTestSheetASL.xlsx (9.1 KB, 16 views)

Just add another logical test to the TRUE side of the IF statement.

=MAX(IF(A2:A19=D2,IF(C2:C19=TRUE,B2:B19)))