Using xlookup across sheets to return data based on cell

[maxwellsdemon13]

Now I'm not sure xlookup is the best formula for this problem but it is the closest I've gotten. Now in the attached book I have a master list of people (column A), their position (B) and how they are ranked by various sources (C through E). Additionally I have three other sheets for each position. I want to us xlookup (or whichever formula is best) to return from the master sheet each player and their ranking based on the position on that sheet (for each non-master sheet the position I want is cell A1). An example of the data I want returned is in the additional sheet "QB (Manual results I want)". So I tried to build xlookup on sheet QB in cell B3 to look like =xlookup(B2,'Master List'!C2:E2,=if('Master List'!B3=QB!A1,'Master List'!A3:E3,0),"",0,1) but most ways I tweak it I don't get any results. I know I am misusing it but I'm not sure in which step, or if I'm even using the right tool, to get the data I want without brute forcing it and manually doing it all.

Since I rarely explain things well, I will try another explanation: I want to search the master list sheet to see if a player has the position listed in cell A1 then return the name and ranking of the player and to keep the rankings aligned by ranking sources in row 2.

Appreciate the help and thank you.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[AliGW]

In A3:

=FILTER('Master List'!A3:A10,'Master List'!B3:B10=A1)

In B3:

=FILTER('Master List'!C3:E10,'Master List'!B3:B10=A1)

[maxwellsdemon13]

Thank you, this does solve my main issue, though I wish there were a way to match ranking sources (B2:D2) in case the order doesn't match page to page but Filter is a function I haven't used to often and does the trick, appreciate it. It is late in my time so I'll give it a test and mark it as solved in a bit, thanks again.

[AliGW]

though I wish there were a way to match ranking sources (B2:D2)

There is a way - give me five.

[AliGW]

In A3:

=FILTER('Master List'!A3:A10,'Master List'!B3:B10=A1)

In B3 copied across and down:

=IFERROR(INDEX('Master List'!$C$3:$E$10,MATCH($A3,'Master List'!$A$3:$A$10,0),MATCH(B$2,'Master List'!$C$2:$E$2,0)),"")

[AliGW]

And here's an all-in-one MS365 version!

In A3:

=FILTER(INDEX('Master List'!A3:E10,SEQUENCE(ROWS('Master List'!A3:E10)),XMATCH(A2:D2,'Master List'!A2:E2)),'Master List'!B3:B10=A1,"No Data")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[maxwellsdemon13]

Thanks for everything.

[AliGW]

You're welcome.