INDEX and MATCH formula

gemmy_85

New Member
Joined
Jun 10, 2019
Messages
3
I need help creating a formula to look up values in order in the rank column in the table on the right, and pullover the Group A country onto the table to the left.

The idea is that I hide the table to the right that is calculating scores based on another area in my spreadsheet, and I will use the one on the left (which will sort the data out automatically and arrange the data in order of points scored).


[TABLE="width: 1036"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Group A[/TD]
[TD]GP[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[TD]GD[/TD]
[TD]PTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Group A[/TD]
[TD]GP[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[TD]GD[/TD]
[TD]PTS[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]Korea[/TD]
[TD]4[/TD]
[TD]:[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]499960004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]Norway[/TD]
[TD]Nigeria[/TD]
[TD]3[/TD]
[TD]:[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Korea[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]-4[/TD]
[TD]2[/TD]
[TD]200040000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]Korea[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Norway[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]499970003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]Norway[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]-3[/TD]
[TD]2[/TD]
[TD]200030000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]France[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Korea[/TD]
[TD]Norway[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Re: Help with INDEX and MATCH formula

I need help creating a formula to look up values in order in the rank column in the table on the right, and pullover the Group A country onto the table to the left.

The idea is that I hide the table to the right that is calculating scores based on another area in my spreadsheet, and I will use the one on the left (which will sort the data out automatically and arrange the data in order of points scored).


[TABLE="width: 1036"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Group A[/TD]
[TD]GP[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[TD]GD[/TD]
[TD]PTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Group A[/TD]
[TD]GP[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[TD]GD[/TD]
[TD]PTS[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]Korea[/TD]
[TD]4[/TD]
[TD]:[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]499960004[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]Norway[/TD]
[TD]Nigeria[/TD]
[TD]3[/TD]
[TD]:[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]Korea[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]-4[/TD]
[TD]2[/TD]
[TD]200040000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]Korea[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Norway[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]499970003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD]###[/TD]
[TD][/TD]
[TD]France[/TD]
[TD]Norway[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]-3[/TD]
[TD]2[/TD]
[TD]200030000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nigeria[/TD]
[TD]France[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Korea[/TD]
[TD]Norway[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Use a vlookup to populate all the information to the left side, vlookup will look based on the country in the left side, and assign the same values from the right table, based on whatever column you want to pull
 
Upvote 0
Re: Help with INDEX and MATCH formula

Thanks but I need it to lookup the country as well as put the table in order of rank, so whilst the table on the right (will be hidden) works out the calculations, the table on the left shows them in order of rank (worked out automatically based on an index match formula but I just can't seem to get it right?)
 
Upvote 0
Re: Help with INDEX and MATCH formula

Thanks but I need it to lookup the country as well as put the table in order of rank, so whilst the table on the right (will be hidden) works out the calculations, the table on the left shows them in order of rank (worked out automatically based on an index match formula but I just can't seem to get it right?)

You can sort the list on the left to rank it, or you can just make a pivot table of the information you need using the table on the right and sort it by ranking
And then you can just refresh it as you need to
To get the pivot table to show like this, you have to go to
Design -> Report Layout -> Show in tabular Form
Design -> Report Layout -> Repeat All item labels
Design -> Subtotals -> Do not show Subtotals


[TABLE="width: 785"]
<tbody>[TR]
[TD]Group A[/TD]
[TD]GP[/TD]
[TD]W[/TD]
[TD]D[/TD]
[TD]L[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[TD]GD[/TD]
[TD]PTS[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Nigeria[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200030000[/TD]
[/TR]
[TR]
[TD]Korea[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200040000[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]499960004[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]499970003[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top