Forum Discussion
X Lookup returning multiple matches
I would change the structure and then do a pivot table.
But I also included a formula solution.
LizM1980 Why not share a workbook that accurately represents what you want to achieve. Then I/we don't have to guess.
Riny_van_Eekelen sorry, I have now added other relevant data. hopefully this helps
LizM1980 Thanks! Not sure I understand what I'm looking at, but I would begin by rearranging the data. Rather than having two rows per person and merged cells, I prefer to use a structured table with one row per person. One column for the name, followed by 5 columns with numbers between 1 and 10, followed by 5 columns that lookup the correct values from another structured table. Then a TOTAL for the 5 lookup results and then a column for the Ranking, sorted Smallest to Largest. Now you can use the first 10 rows for a Top 10 listing.
I would change the structure and then do a pivot table.
But I also included a formula solution.
- oh yeah, of course. Why didn't I think of that! Thankyou