Lowest number of corresponding category that is part of a group

[jrm0523]

I have a category. Let's call it fruits. The category fruits includes: bananas, apples, oranges, grapes.

In a separate sheet, I have a list of text cells with corresponding values. Example:


C5____D5____E5_____F5___G5______H5
Apples__3____Steak___2___Oranges__4

So, Apples corresponds with 3
Steak corresponds with 2
Oranges corresponds with 4


I want to take the lowest value of a fruit. Since steak is not a fruit, the return I want is "3." Any ideas?


Thank you very much!

[DGagnon]

to do this you need to have a list of what is fruit, and what is not.. could you attach a copy of the workbook you are working with?

[jrm0523]

Everything on my workbook is confidential, but attached is an example.

Thanks again!

[DGagnon]

Try this array formula (confirm with Ctrl+Shift+Enter)

note the offset in the E:M range and the F:N range

=MIN(IF(E3:M3=A$2:A$6,F3:N3,9^9))

[DGagnon]

Duplicate post, please delete

[jrm0523]

That worked. Thanks a ton!

One more issue. I want to also return the maximum value, and some of the cells are blank (they have =""). When I change from MIN to MAX, I am not receiving the right answer (it is returning something like 24324329). How do I fix this?

Also, what is the purpose of the 9^9 as opposed to just leaving that blank?


Thanks again. New file attached to show example.

[DGagnon]

you would need to cahnge the 9^9 to a 0 to get max, what that does is make it so when ever this is not a match in the min formula it will return a very high number, you could just leave that blank. the max formula should also work if it is blank.