d

Automatically update ranking list in Excel


Auto update the ranking list in Microsoft Excel using of INDEX(), MATCH() and LARGE() function









Ok let’s start first we make a list of the employee name with sales value

In routine work everyone use the filter to sort the data using A-Z or Z-A for raking.




If we miss/forget the updating or sort the data and the result is ….?
Let’s make list


And make the list or table with containing Rank, Name and Sales Value column 


Type the rank in ascending order 1 to 6
In cell J3 type the formula =INDEX($D$3:$D$8,MATCH(LARGE($E$3:$E$8,I3),$E$3:$E$8,0))
INDEX(select_ data_name_and_sales)
And select the cell D3 to D8





And type , and start MACH()function, type MATCH(

The match function matching the data from index and the large function select the largest data



MATCH(LARGE(name_list,rank_number),rank_name,0 and close ))

After this now the
=INDEX($D$3:$D$8,MATCH(LARGE($E$3:$E$8,I3)








And see the result





 Copy J3 cell and pate it to J3 to J8





 And now get the sales value data for Prem.

This formula only gets the name rank and now we need the remaining data
Here we can use the VLOOKUP() function
In cell K3 type the formula =VLOOKUP($J3,$D$3:$E$8,2,0)
VLOOKUP(result_of_name_rank, select_ data_name_and_sales,2 second value, 0 and close)

Type K3 =VLOOKUP(select the J3{with $ sing for lock the J Column}



And select the data from D3:E9 {D with $ sing for lock the D Column}


And type after comma 2 {2 is the indicate the second data after the lookup value, if you need the get the data in 3rd column type 3 instead of 2}


And after the column index just type 0,


 And the result appear in K3 you will see in below picture

And copy the K3 and past it to data as required




The result show in figure below……




No comments:

Post a Comment

Recommended for You

LinkWithin

Related Posts Plugin for WordPress, Blogger...