Jun 30 2022 03:44 AM
Hello - looking for an Excel whizz.
Trying to complete Cell J3 with a column header based on the player and their 'score' from the week.
I have tried a combination of LOOKUP with INDEX MATCH but not quite getting it.
Any he
Jun 30 2022 06:14 AM
Solution@GPet18 Here are 2 options that should work. The 1st uses Table Reference (update 'Table1' to the name of your table if it is different) and is preferred since then the table can grow and you don't have to worry about adjusting the formula:
=INDEX(Table1[#Headers],MATCH($J$2,INDEX(Table1[#Data],MATCH($J$1,Table1[Player],0),),0))
This second uses the traditional cell references and you could make them really big (e.g. A1:G10000) but that is a waste and inefficient.
=INDEX($A$1:$G$1,MATCH($J$2,INDEX($A$1:$G$5,MATCH($J$1,$A$1:$A$5,0),0),))
Jun 30 2022 08:34 AM
@mtarler Thank you so much for the help.
I am afraid I am still getting an error (#N/A).
Your table obviously works, but when I typed a number into J2 that was not identical to a number within the [#Data], the formula provided an error.
I am so sorry to feed this back considering your efforts for me.
All the best,
George
Jun 30 2022 09:59 AM
Jun 30 2022 11:33 AM
By way of warning, I use Excel 365 and only develop code for that version.
= LET(
playerData, XLOOKUP(player,ResultTbl[Player],ResultTbl),
XLOOKUP(score, playerData, ResultTbl[#Headers],"Out of bounds",-1))
Jul 01 2022 08:32 AM
Jul 01 2022 08:32 AM
Jun 30 2022 06:14 AM
Solution@GPet18 Here are 2 options that should work. The 1st uses Table Reference (update 'Table1' to the name of your table if it is different) and is preferred since then the table can grow and you don't have to worry about adjusting the formula:
=INDEX(Table1[#Headers],MATCH($J$2,INDEX(Table1[#Data],MATCH($J$1,Table1[Player],0),),0))
This second uses the traditional cell references and you could make them really big (e.g. A1:G10000) but that is a waste and inefficient.
=INDEX($A$1:$G$1,MATCH($J$2,INDEX($A$1:$G$5,MATCH($J$1,$A$1:$A$5,0),0),))