-1

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

player0
  • 124,011
  • 12
  • 67
  • 124
LADD
  • 95
  • 8

1 Answers1

2

try:

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

enter image description here

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)))

enter image description here

or shorter:

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

enter image description here

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)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • The Players "CODE" is their personal ID for the system that no one should be able to see. That's why I wanted to nest the "Unique" function into the "Name" and "Nick Name" functions. So Column A isn't visible. Any suggestions on how to Nest it? Or another way to hide the "QRCode" information while still pulling only the unique players from the (SloppyCheckin) page? – LADD Aug 15 '20 at 15:52
  • @LavellDishmon answer updated. see your sheet and las picture – player0 Aug 15 '20 at 17:57