MATCH function to find position of the result of NameRange

[ififthelement]

Hi All,

I have 3 user defined Names - ExecWinchrome, ExecWinFireFox, ExecWinIE.

I need a function that tells me which one of these has minimum value and then find the position. In turn based on the position, I wast to display the Name

I tried the following

=Match(MIN(ExecWinchrome, ExecWinFireFox, ExecWinIE), {ExecWinchrome, ExecWinFireFox, ExecWinIE}, 0)

The Lookup_Array in RED gives error. How do I tell Excel that its an array of the result of the Name variable ?

if the result of MATCH is 1, i will shows Win-chrome; if 2 I will show Win-FF, if 3, I will show WinIE.

Instead of those variable in RED, if I directly put the number like {100,89,95} is tell me the correct result of MATCH, because the MIN function does its work perfectly.

Please suggest!

 

[ififthelement]

Hi XOR,

it returned a single number from the {1,2,3} of CHOOSE.. thats wat I wanted.

Now, is there a way to avoid a lot of IF..ELSE around the =MATCH(MIN(ExecWinchrome,ExecWinFireFox,ExecWinIE),CHOOSE({1,2,3},ExecWinchrome,ExecWinFireFox,ExecWinIE),0) so that I can relate that number to a text..

If the result of above Match = 1 then show text "Win-Chrome", else If result is 2, then show "Win-FF", same way. I doubt if there is any Case function.. or do I have to write lotsa IF .... But then is there any way that I can get the result of above Match once and check the case.

or VBA I have to create a VBA function.. that runs the Select..Case and returns the text ? but then how will I Pass the MATCH functon (given above)

 

[Aladin Akyurek]

these are user defined names. Each of them will return an integer only

Domenic suggestion should work with such. The formula requires control+shift+enter, not just enter.
The same holds for a formula with CHOOSE instead of INDIRECT. But, if the MIN value holds for more than one name, you'll be in trouble, unless...

For correctness, you need something more.

Activate Formulas | Name Manager.
Activate the New tab.
Enter BROWSERS ain the Name box.
Enter the following formula in the Refers to box:

=CHOOSE({1,2,3},"WinChrome","WinFireFox","WinIE")

Click OK.

Define BVALUES likewise as referring to:

=CHOOSE({1,2,3},ExecWinChrome,ExecWinFireFox,ExecWinIE)

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(BROWSERS,SMALL(IF(BVALUES=MIN(BVALUES),{1,2,3}),ROWS($A$2:A2))),"")

 

[ififthelement]

Ok.. I got it .. I have put the entire MATCH into another Variable and not I can do IF..ELSE .. that looks smaller now

thanks guys for your support.. XOR .. u ROCK !

 

[XOR LX]

Domenic suggestion should work with such.

I'm interested in this statement. Can you clarify what you mean?

If the Defined Names are not references to actual worksheet ranges, but to numerical values, which appears to be the case here, then I don't see how this can be the case, since INDIRECT will not accept the latter for its ref_text argument.

Regards

 

[Aladin Akyurek]

@ififtheelement

Consider the situation in which we have ExecWinChrome = 3, ExecWinFireFox = 2, and ExecWinIE = 2.