Struggling so much with INDEX MATCH

[Steviefiege]

Hi,
One day i'll get my head around how to you these 2 functions properly.

I want to find my best Goalkeeper out of a list of players. In my sheet so far i have the position & the highest rating for that position, i just need to match the position with the rating to get the player. Sounds so easy, i can just never get my head around it.

<tbody>
[TD="class: xl65, colspan: 2"]Goalkeeper[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl67, width: 64"]55.77[/TD]

</tbody>

[TABLE="width: 256"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="class: xl67"]Kongolo, Rodney[/TD]
[TD="class: xl69, width: 130"]Advanced Playmaker[/TD]
[TD="class: xl71, width: 89"]45.00[/TD]

[TD="class: xl67, width: 200"]Kongolo, Rodney[/TD]
[TD="class: xl69"]Attacking Midfielder[/TD]
[TD="class: xl71"]47.92[/TD]

[TD="class: xl67, width: 200"]Kongolo, Rodney[/TD]
[TD="class: xl69"]Advanced Playmaker2[/TD]
[TD="class: xl71"]45.00[/TD]

[TD="class: xl68, width: 200"]Lawlor, Ian[/TD]
[TD="class: xl69"]Goalkeeper[/TD]
[TD="class: xl71"]55.77[/TD]

[TD="class: xl67, width: 200"]Leverton, James[/TD]
[TD="class: xl69"]Goalkeeper[/TD]
[TD="class: xl71"]35.00[/TD]

[TD="class: xl67, width: 200"]Lund, Mitchell[/TD]
[TD="class: xl69"]Full Back (Right)[/TD]
[TD="class: xl71"]54.29[/TD]

[TD="class: xl67, width: 200"]Lund, Mitchell[/TD]
[TD="class: xl69"]Central Defender[/TD]
[TD="class: xl71"]51.25[/TD]

</tbody>

Rgs
Steve

 

[Nine Zero]

lets see if we can break it down a bit to make it easier to understand

lets assume the table is from A1 to C7

and your formula you want it to be in G1

=INDEX(A,MATCH(B,C,D))

A = The range of where your result is located, in this example case it would be A1:A7

B = What you want to look up since you have multiple criteria meaning the position and the score you have to separate them with the & sign ... so for this example it would be F1&H1

C = Where you want to look up those value respectively again if multiple criteria, separate with & sign, example above would be B1:B7&C1:C7

D = a 0 for exact match

so in conclusion something like this

=INDEX(A1:A7,MATCH(F1&H1,B1:B7&C1:C7,0))

Also this is an array formula so instead of just hitting enter, you must do CTRL + SHIFT + ENTER

 

[Jeffrey Mahoney]

Try this:
=INDEX(A1:C7,MATCH(MAX(C1:C7),C1:C7,0),2)

 

[Steviefiege]

Nine Zero, you're a diamond. Definately going to save that post as i understood what it meant. Whats with the arrays though, the {}, what do they do?

 

[nardagus]

I came up with a nice formula too.

Let's assume you look for the best goalkeeper, but you don't know his score.
This will find the best score within all goalkeepers only, and then will return a name of a player:


Excel 2007

	A	B	C
Lawlor, Ian

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Kongolo, Rodney[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Advanced Playmaker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Kongolo, Rodney[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Attacking Midfielder[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]47,92[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Kongolo, Rodney[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Advanced Playmaker2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Lawlor, Ian[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Goalkeeper[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]55,77[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Leverton, James[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Goalkeeper[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]35[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Lund, Mitchell[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Full Back (Right)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]54,29[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Lund, Mitchell[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Central Defender[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]51,25[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

SHEET1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]A11[/TH]
[TD="align: left"]{=INDEX(A1:C7,MATCH(MAX(IF(B1:B7="Goalkeeper",C1:C7,0)),C1:C7,0),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[nardagus]

Oops. I found a bug in my formula. I cannot edit my post anymore.
The bug is, that when two players, on a different positions, have the same highest score it will give you a name of the first player with that score. It can be either goalkeeper or advanced playmaker. Whoever is first.
This will give you a proper name:

Excel 2007

	A
Lawlor, Ian

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]

</tbody>

SHEET1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A11[/TH]
[TD="align: left"]{=INDEX(A1:A7,MATCH("Goalkeeper"&MAX(IF(B1:B7="Goalkeeper",C1:C7,0)),B1:B7&C1:C7,0),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Another option if you have Excel ver. 2010 or later with the AGGREGATE function. This formula does not require CTRL-SHIFT-ENTER, just ENTER.

Excel Workbook
	A	B	C
1	Kongolo, Rodney	Advanced Playmaker	45
2	Kongolo, Rodney	Attacking Midfielder	47.92
3	Kongolo, Rodney	Advanced Playmaker2	45
4	Lawlor, Ian	Goalkeeper	55.77
5	Leverton, James	Goalkeeper	35
6	Lund, Mitchell	Full Back (Right)	54.29
7	Lund, Mitchell	Central Defender	51.25
8
9
10	Goalkeeper	Lawlor, Ian	55.77
Sheet

 

[Steviefiege]

I like it!!!

 

[RockandGrohl]

I've got it written down so it's like:

INDEX(B3:F3,MATCH(A8,B4:F4,0) - these aren't pertinent to your table.

So B3:F3 is the index - it's the list where your results desired are.
A8 is the Match, or the lookup, like in Vlookup.
B4:F4 is where the lookup is found.


So with an Index Match you tell the formula where your index is, what your match criteria is, then where your match criteria is found.