Index Match formula help

[Neilesh Kumar]

Dear Experts,

Please find here attached sample workbook, The formula which i am currently using is giving only the unique sales person name in Sheet1 tab from Cell G10:G12, I am currently looking for the Index Match with Highest value and accordingly i want to appear first that sales person name according to their ascending to descending sales order.

The expected results have been given in Sheet1 tab from Cell K10:K12.

Request to you kindly do provide your precious expertise on the same. Your valuable support would be appreciated.

Many thanks and kind regards,

Neilesh

Sample Workbook.xlsx

[Billy Spivy]

I hope this is what you had in mind:

{=IFERROR(INDEX(Sheet2!$M$3:$M$10,MATCH(Sheet1!$G16,Sheet2!$D$3:$D$10,0)),"")}

CTRL+SHIFT+ENTER for the curly brackets

[Neilesh Kumar]

Hi Billy, Thank you so much for your precious phase and valuable support. I do have formula already provided in my sample workbook, I am looking for the formula to arrange from ascending to descending order based on their total Sales Value. I hope you are getting my expectations. from Cell K10:K12 in Sheet1 i have provided the expected results.

Request to you please do help me out.

Many thanks and kind regards,

Neilesh

[Billy Spivy]

All you have to do is arrange the sales people in the order of your choosing. Then the array will match the value to that salesperson in the adjacent column. I put the example below your table. If you want I can put in the appropriate cells for you.

[Neilesh Kumar]

Is there any way that by using the formula Sales Person name with the highest sales number will come at the top and then according the rest other sales person will appear according to the sales numbers order.

[Billy Spivy]

I changed the 2nd table to show how to sort highest to lowest.

[Neilesh Kumar]

Dear Billy, In the provided solution i needs to create two separate tables for sorting the highest sales value, sales person name. Is there any way to upgrade the current formula in Sheet1 cell G10:G12. so the Sales person name will arrange in ascending order by their Sales numbers.

[Glenn Kennedy]

Are your expected results... AS WELL AS the table on the left...or INSTEAD OF the table on the left?

[Neilesh Kumar]

Dear Experts,

I found a formula but it is not exactly what i am looking for because it is not providing in the descending order but it is giving the fixed result only a top sales person name. So can you please do upgrade with my current requirement.

Please Login or Register  to view this content.

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

Are your expected results... AS WELL AS the table on the left...or INSTEAD OF the table on the left?

Dear Expert,

In my expected results Cell K10:K12 i am looking for the Sales person name from the highest sales value to the Descending order.

[Glenn Kennedy]

That does not answer my question. I understand what you want.... Can the results be calculated from the table on the left, or must they be calculated from the raw data?

[Neilesh Kumar]

Sir i am expecting from the raw data.

[finalazy]

How about =INDEX(Sheet2!D:D,MATCH(LARGE(Sheet2!$M$3:$M$14,ROW(A1)),Sheet2!M:M,0)) for the Sale person name
and =INDEX(Sheet2!M:M,MATCH(LARGE(Sheet2!$M$3:$M$14,ROW(A1)),Sheet2!M:M,0)) for the net license Rev.

[Neilesh Kumar]

Dear Finalazy it is not summing up the numbers, In case if the sales person name is repeating. Can you please look into the same.

[finalazy]

Dear Finalazy it is not summing up the numbers, In case if the sales person name is repeating. Can you please look into the same.

You are welcome. Thanks to billy, i just tweaked a bit of his formula. Do help to change the thread to solve =)

[Neilesh Kumar]

How about =INDEX(Sheet2!D:D,MATCH(LARGE(Sheet2!$M$3:$M$14,ROW(A1)),Sheet2!M:M,0)) for the Sale person name
and =INDEX(Sheet2!M:M,MATCH(LARGE(Sheet2!$M$3:$M$14,ROW(A1)),Sheet2!M:M,0)) for the net license Rev.

Dear Finalazy, The provided formula is repeating the sales persons name based on their row value. Is it possible to sum up with a single sales person name.

[finalazy]

Hi Neilesh,

I don't think thats possible without a few more column beside the table. VBA may be able to do it though. Another way is to use the consolidate function at the tool bar

[Neilesh Kumar]

Dear Finalazy, I found the formula which I have provided in my current thread but only the matter is the found formula is providing with the top sales person name n not the rest others.

[finalazy]

Hi Neilesh,

without additional helper column, i'm not sure how i can remove the duplicates. But i tweaked it to show the top sales name with his Final net license rev only first.

try putting this at Sales person column =INDEX(Sheet2!$D$3:$D$1000,MATCH(LARGE(SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$M$3:$M$1000),ROW(A1)),SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$M$3:$M$1000),0)) CTRL SHIFT ENTER

then Net License Rev
=SUMIFS(Sheet2!M:M,Sheet2!D:D,R14) <--- note on the R14, change it the cell that you paste for the sales person. Example K16. It will get the final net sum only for the sales person.

[Neilesh Kumar]

Dear Finalazy, Its providing the results but it is not removing the duplicates. In my actual working sheet Sales person names are getting repeat but in the provided sample workbook in the forum i have not repeated the Sales person names.

[Neilesh Kumar]

I feel including Countif in the formula may work but i am not sure how to mix with the formula

[finalazy]

Hi Neilesh,

I agree countif will work it out but I will require additional column. I'm not sure how it can mix it in one formula as well.
Maybe this video can give you some idea. https://www.youtube.com/watch?v=IZLAzIYfMDU

[Neilesh Kumar]

Hi Finalazy, Thank you for your precious support but the provided link does not work for me. Thank you so much once again.

[Neilesh Kumar]

Hi Neilesh,

I agree countif will work it out but I will require additional column. I'm not sure how it can mix it in one formula as well.
Maybe this video can give you some idea. https://www.youtube.com/watch?v=IZLAzIYfMDU

Hi Finalazy, can you please add a required column n do provide the formula incl Countif.

[finalazy]

Hi Neilesh,

Added few column at sheet 2. See if this work out for you.

[Neilesh Kumar]

Hi Neilesh,

Added few column at sheet 2. See if this work out for you.

Hi Finalazy, Thank you so much for your Valuable support and precious phase. The provided solution is working perfect but it requires to change lot of formulae in my Dashboard, which contains approx. 12 Tabs. Once again thank you so much.

So there is no way to arrange the Sales Rep. names based on their Sales value in ascending to descending order.

[JohnTopley]

Which sheet(s)/"Sales Rep" columns: do you mean "Sheet2" should be reordered by "Sales Value"?

[Neilesh Kumar]

Which sheet(s)/"Sales Rep" columns: do you mean "Sheet2" should be reordered by "Sales Value"?

Dear Expert, Please find here attached Sample Workbook, where i am expecting the formula in Sheet1 Cell K10:12 Sales persons unique name in from top sales person to descending order by their total Sales from Sheet2.

Request to you please do help me out. Many thanks and kind regards,

Neilesh

Sample Workbook_1.xlsx

[Neilesh Kumar]

I found a formula but this is fixed with the top sales person name and not giving in descending order Sales person name.

Please Login or Register  to view this content.

[finalazy]

Hi Neilesh,

try this sample. I think this is what you looking for.

[Glenn Kennedy]

Name (array):
=INDEX(Sheet2!$D$3:$D$1000,MATCH(LARGE(SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$k$3:$K$1000),ROWS(M$10:M10)),SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$M$3:$M$1000),0))

Amount:
=SUMIF(Sheet2!D:D,Sheet1!M10,Sheet2!M:M)

[Neilesh Kumar]

Hi Neilesh,

try this sample. I think this is what you looking for.

Awesome. Yes Dear Finalazy, Exactly the same i was looking for. Perfect Dear. Thank you so much. Really appreciate your valuable support.

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

Name (array):
=INDEX(Sheet2!$D$3:$D$1000,MATCH(LARGE(SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$k$3:$K$1000),ROWS(M$10:M10)),SUMIF(Sheet2!$D$3:$D$1000,Sheet2!$D$3:$D$1000,Sheet2!$M$3:$M$1000),0))

Amount:
=SUMIF(Sheet2!D:D,Sheet1!M10,Sheet2!M:M)

Awesome Dear Expert. Perfect Solution. Exactly the same i was looking for Sir.

Many thanks and kind regards,


Neilesh

[finalazy]

Awesome. Yes Dear Finalazy, Exactly the same i was looking for. Perfect Dear. Thank you so much. Really appreciate your valuable support.

Many thanks and kind regards,

Neilesh

Glad it helps. Do kindly help to change thread to solve =)

[Glenn Kennedy]

Here's a much better solution than my first effort:

=INDEX(Sheet2!$D$3:$D$10,MATCH(L10,INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),0))

and

=LARGE(INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),1)

both ordinary formulae.

[finalazy]

Here's a much better solution than my first effort:

=INDEX(Sheet2!$D$3:$D$10,MATCH(L10,INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),0))

and

=LARGE(INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),1)

both ordinary formulae.

Nice Glenn. I prefer your method which does not make use of array. Much better!

[Neilesh Kumar]

Here's a much better solution than my first effort:

=INDEX(Sheet2!$D$3:$D$10,MATCH(L10,INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),0))

and

=LARGE(INDEX(SUMIFS(Sheet2!$M$3:$M$10,Sheet2!$D$3:$D$10,Sheet2!$D$3:$D$10)*(COUNTIF($K$9:$K9,Sheet2!$D$3:$D$10)=0),0),1)

both ordinary formulae.

Thank you so much Glenn Sir. I do appreciate your valuable support.

Many thanks and kind regards,

Neilesh