INDEX and MATCH formula

G

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Re: Help with INDEX and MATCH formula

gemmy_85 said:
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]
Click to expand...

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

gemmy_85 said:
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?)
Click to expand...

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
You must log in or register to reply here.

Similar threads

K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
151
kerm007
K
L
  • Solved
Index Match with 3 Criteria
Replies
5
Views
90
etaf
etaf
C
  • Question Question
Alternative to Index Match to sum up
Replies
4
Views
212
Fluff
Fluff
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
812
FormR
FormR
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
89
sijpie
sijpie

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

Share this page

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