Why is nesting the "unique" function not working?

Question

So I've put a few hours into this and I've finally hit a sticking point that I can't figure out. I'm using a players "Code" to pull information from 1 sheet to another. Normally this would be easy but before I pull the information, I'm running it through the unique function to get a list individual (NON repeating list) of players. It seems like this unique functions is throwing things off.

So I'm using the unique function to get a singular list of "Codes". Then using those codes to return "First Name" and "Nick Name". The issue comes when I try to nest the Unique function within the DGET code.

I've tried using DGET, Vlookup and QUERY with no luck.

PS. This is also a dynamic field so I need to have it automatically update with new inputs. Tried ArrayFormula with no luck :/

=dget(
    importrange("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", 
    "PlayerRegistration!B1:G"),"Nick Name",
       {"QR Code Reader";(
            unique(importrange("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ",
            "PlayerRegistration!G2:G"))
                          )
       }
)

If you could help, that would be awwwwweesome !!! Below is a link to the sheets.

https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, {
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!G:G"),
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!D:D")}, 2, 0)))

---

or with names in one go:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, {
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!G:G"),
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!B:B"),
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!D:D")}, {2, 3}, 0)))

---

or shorter:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, QUERY(
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!B:G"),
 "select Col6,Col1,Col3", 0), {2, 3}, 0)))

---

nested:

=ARRAYFORMULA(IFNA(VLOOKUP(UNIQUE(
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "SloppyCheckin!B2:B")), QUERY(
 IMPORTRANGE("1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ", "PlayerRegistration!B:G"),
 "select Col6,Col1,Col3", 0), {2, 3}, 0)))