Extracting Data from Multiple Worksheets

Here I am again asking for help. Each time I have came here for assistance my issue has been resolved so I am feeling optimistic. I pretty sure my question should be easy to answer all you excel experts on here.

So, I am currently working on a doc for UFC fighters with multiple tabs. The Data tab is where I input the all fighter results with a certain scoring criteria for each fight. Other tabs include, Stats, Fighter Search, and updated rankings from each division (1 tab for each division). The data from these rankings tabs are extracted from Wikipedia.com.

What I am struggling with is adding a ranking column to my data tab in column A. I would like to extract the rankings from from the rankings tabs to the corresponding fighter in the data tab. For those fighters that aren't ranked (the majority) I would for it to display "-". Hopefully this makes sense.

As you can see I have tried INDEX, MATCH formula but it has only worked for the flyweight division fighters and an error occurs for the fighters that are ranked in the other divisions. I assume there is a simple way of doing this but I am can't figure it out.

Please have a look at my spreadsheet attached to have a better understanding of my problem. I realise that the spreadsheet isn't perfect but it is a work in progress.

Just to clarify. I would like Column A in the data tab to show the ranking of each fighter (if any) which can be found in Column A on tabs Flyweight - Woman's Bantamweight. For those who are ranked I would like it show their ranking (e.g. C for champion or 1-15) and for those that aren't ranked "-".

**My file has failed to upload. I assume its' because it's too big. I will create a smaller sample doc. In the meantime if anyone can help without needing to see my doc then that would be amazing!!**

***NEW SAMPLE FILE NOW ADDED*** It is a rather simplistic version of the original but should be enough. Column A on Sheet 1 is where I would like the new formula. So the first few cells should equal...
A2 = 4
A3 = 12
A4 = 1
A5 = 10
A6 = 3
A7 = 3
A8 = 10
A9 = C
A10 = 1
A11 = 12
A12 = -

Hopefully this all makes sense.

Thanks in advance.

You have to be kidding!! we need to see the file. The filessize limit for .xlsx if 1 Mb. As a .zip or .xlsb it is much higher.

My suggestion is to utilize Get and Transform to produce a single table (AllClassifications) using the following advanced editor code:

Please Login or Register  to view this content.

The formula that finds the ranking is: =IFERROR(INDEX(AllClassifications[Rank],MATCH(B2,AllClassifications[Fighter],0)),"-")
Let us know if you have any questions.

Change the table name in the PQ Editor to whatever you named your table. Possibly Table 1?

Originally Posted by alansidman

Change the table name in the PQ Editor to whatever you named your table. Possibly Table 1?

Hi thanks for the message.

I created a table from blank query so I haven't previously named a table. I hope that makes sense?

Let me know if you have any other suggestions.

Cheers

If you didn't name the query table, I imagine that Excel named it "Query 1". If so put that in the place of "AllClasifications" in the #"Filter Rows" line.
If that doesn't work then I suggest uploading a sample of the workbook wherein the error is occurring.

Made a modification to the code:

Please Login or Register  to view this content.

The formula for column A on the Data sheet is: =IFERROR(INDEX(AllClassifications[Rank],MATCH(B5,AllClassifications[Fighter],0)),"-")
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.