INDEX and MATCH formula

[gemmy_85]

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).

Group A

GP

W

D

L

GF

GA

GD

PTS

Group A

GP

W

D

L

GF

GA

GD

PTS

Rank

###

###

###

###

###

###

###

###

France

Korea

4

:

0

France

3

1

2

0

4

0

4

5

499960004

###

###

###

###

###

###

###

###

Norway

Nigeria

3

:

0

Korea

3

0

2

1

0

4

-4

2

200040000

###

###

###

###

###

###

###

###

Nigeria

Korea

:

Norway

3

1

2

0

3

0

3

5

499970003

###

###

###

###

###

###

###

###

France

Norway

:

Nigeria

3

0

2

1

0

3

-3

2

200030000

Nigeria

France

:

Korea

Norway

:

<tbody>
</tbody>

 

[Twollaston]

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).

Group A

GP

W

D

L

GF

GA

GD

PTS

Group A

GP

W

D

L

GF

GA

GD

PTS

Rank

###

###

###

###

###

###

###

###

France

Korea

4

:

0

France

3

1

2

0

4

0

4

5

499960004

###

###

###

###

###

###

###

###

Norway

Nigeria

3

:

0

Korea

3

0

2

1

0

4

-4

2

200040000

###

###

###

###

###

###

###

###

Nigeria

Korea

:

Norway

3

1

2

0

3

0

3

5

499970003

###

###

###

###

###

###

###

###

France

Norway

:

Nigeria

3

0

2

1

0

3

-3

2

200030000

Nigeria

France

:

Korea

Norway

:

<tbody>
</tbody>

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

 

[gemmy_85]

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?)

 

[Twollaston]

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

Group A	GP	W	D	L	GF	GA	GD	PTS	Rank
Nigeria	3	0	2	1	0	3	-3	2	200030000
Korea	3	0	2	1	0	4	-4	2	200040000
France	3	1	2	0	4	0	4	5	499960004
Norway	3	1	2	0	3	0	3	5	499970003

<tbody>
</tbody>

 

[gemmy_85]

Re: Help with INDEX and MATCH formula

Thank you so much I will try that

 

[Twollaston]

Re: Help with INDEX and MATCH formula

Thank you so much I will try that

No problem, Happy to help you