How can i join these two formulas?
 

I have two working formulas and i need to put them both together in one
string. Can anyone please help. i want to display the name of the top seller.
The first formula was the orginal and now i have more employees and the 2nd
formula does the same thing. All cells referrals need to stay the same.

Formula 1: =INDEX(A10:A14,MATCH(MAX(H10:H14),H10:H14,0))
Formula 2: =INDEX(P10:P16,MATCH(MAX(V10:V16),V10:V16,0))

How can i join these two formulas?
 

Try the below version

=IF(ISNA(MATCH(MAX(H10:H14,V10:V16),H10:H14,0)),IN DEX(P10:P16,
MATCH(MAX(V10:V16),V10:V16,0)),INDEX(A10:A14,
MATCH(MAX(H10:H14),H10:H14,0)))

--
Jacob


"Frustrated" wrote:

I have two working formulas and i need to put them both together in one
string. Can anyone please help. i want to display the name of the top seller.
The first formula was the orginal and now i have more employees and the 2nd
formula does the same thing. All cells referrals need to stay the same.

Formula 1: =INDEX(A10:A14,MATCH(MAX(H10:H14),H10:H14,0))
Formula 2: =INDEX(P10:P16,MATCH(MAX(V10:V16),V10:V16,0))

How can i join these two formulas?
 

You can't combine them because they reference different ranges.

Either consolidate the data to a single contiguous range or...

A possibility is to download and install the free add-in Morefunc.xll from

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/300...-10423159.html

It has a function that will allow you to combine those different ranges into
a single range.

=INDEX(ARRAY.JOIN(A10:A14,P10:P16),MATCH(MAX(ARRAY .JOIN(H10:H14,V10:V16)),ARRAY.JOIN(H10:H14,V10:V16 ),0))

--
Biff
Microsoft Excel MVP


"Frustrated" wrote in message
...
I have two working formulas and i need to put them both together in one
string. Can anyone please help. i want to display the name of the top
seller.
The first formula was the orginal and now i have more employees and the
2nd
formula does the same thing. All cells referrals need to stay the same.

Formula 1: =INDEX(A10:A14,MATCH(MAX(H10:H14),H10:H14,0))
Formula 2: =INDEX(P10:P16,MATCH(MAX(V10:V16),V10:V16,0))

How can i join these two formulas?
 

Improvement...

We can eliminate the ARRAY call within MAX:

=INDEX(ARRAY.JOIN(A10:A14,P10:P16),MATCH(MAX(H10:H 14,V10:V16),ARRAY.JOIN(H10:H14,V10:V16),0))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You can't combine them because they reference different ranges.

Either consolidate the data to a single contiguous range or...

A possibility is to download and install the free add-in Morefunc.xll from

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/300...-10423159.html

It has a function that will allow you to combine those different ranges
into a single range.

=INDEX(ARRAY.JOIN(A10:A14,P10:P16),MATCH(MAX(ARRAY .JOIN(H10:H14,V10:V16)),ARRAY.JOIN(H10:H14,V10:V16 ),0))

--
Biff
Microsoft Excel MVP


"Frustrated" wrote in message
...
I have two working formulas and i need to put them both together in one
string. Can anyone please help. i want to display the name of the top
seller.
The first formula was the orginal and now i have more employees and the
2nd
formula does the same thing. All cells referrals need to stay the same.

Formula 1: =INDEX(A10:A14,MATCH(MAX(H10:H14),H10:H14,0))
Formula 2: =INDEX(P10:P16,MATCH(MAX(V10:V16),V10:V16,0))

How can i join these two formulas?
 

On Dec 23, 12:34*am, Frustrated
wrote:
I have two working formulas and i need to put them both together in one
string. Can anyone please help. i want to display the name of the top seller.
The first formula was the orginal and now i have more employees and the 2nd
formula does the same thing. All cells referrals need to stay the same.

Formula 1: =INDEX(A10:A14,MATCH(MAX(H10:H14),H10:H14,0))
Formula 2: =INDEX(P10:P16,MATCH(MAX(V10:V16),V10:V16,0))

try this,
=IF(MAX(H10:H14)MAX(V10:V16),INDEX(A10:A14,MATCH( MAX
(H10:H14),H10:H14,0)),INDEX(P10:P16,MATCH(MAX(V10: V16),V10:V16,0)))