Find Min within Range

[Emydee]

I have a very similar issue to the one above. I have 4 values that I was able to match with numbers by adjusting the formula you provided as my location is different. My question is, I was able to match them when they were next to each other, ie A=1, B=2 and get the min, but how would I find the min in a different range making the text still equal those numbers??

[NBVC]

Please elaborate... as I had to move your post to its own thread....

[Emydee]

Disregard my msg, I just saw that this is the new thread...coffee hasn't set in and I've been staring at this file for days, so my mind is a little blurry.

Per this link http://www.excelforum.com/excel-new-...-function.html, you were able to help that person match up the headers with corresponding values. I used the formula you provided and adjusted it slightly for my parameters to make the text equal the number values. What I did was take Platinum, Gold, Silver, and Not to Standard, and put 1, 2, 3 and 4 in the cells next to them (in BD45 in attached file), making Platinum=4, Gold=3, Silver=2 and Not to Standard=1. Now what I need to do is look in a different range for the text values (C12:C41) and return the minimum corresponding with those numbers. So let's say that as per the attached, platinum appears 2x, Gold 1x and silver 1x. Since silver is the lowest, I need C45 to show "Silver". That way, even if there are 3 platinums and one of the other options, the lowest will appear. This sheet will be duplicated many times, so rather than have to manually put the lowest option in, I think its easier via a formula.

[NBVC]

Try this:

In BF45 enter formula:

Please Login or Register  to view this content.

and copy down

Then in C45 enter formula:

Please Login or Register  to view this content.

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.. You will see { } brackets appear around it.

[Emydee]

So do it as an array formula, correct?

[NBVC]

yes, that's right.

[Emydee]

I just tried the formula and got an #NA. When I go to view the error it says that after MATCH there is a 0 and its italicized. Any suggestions?

[NBVC]

Here is your workbook with the formulas in it....working.