1

I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link:

https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=1738544316

What I'm trying to have it do, is search the "Stats Gathering" sheet for the value in the C Column. When it finds that value, I want it to return the value of the A Column on the "Stats Gathering" sheet, i.e. their name.

I looked up a guide on youtube and it told me to importrange, which i did. This is the formula I've been using:

=VLOOKUP(C3, importrange("193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk", "Stats Gathering!A3:W43"), 1, FALSE)

Any idea what I'm doing wrong?

EDIT: Forgot to mention. The error it's giving says it can't find the value '10.5' in VLOOKUP evaluation. So essentially, anything in the C column, it says it can't find it in the importrange, even though it's in there.

0

Instead of 249 static formulas you can use just 3 dynamic ones.

For your 1st set use the following on cell A3

=QUERY(ArrayFormula(((IFERROR( 
       VLOOKUP(SORT('solution Stats Gathering'!K3:K,1,0),
                  ({'solution Stats Gathering'!K3:K,'solution Stats Gathering'!A3:L}),{2,3,12},0)
      )))),"select * where Col1 is not null")

For the 2nd set use this one

=QUERY(ArrayFormula(((IFERROR( 
       VLOOKUP(SORT('solution Stats Gathering'!I3:I,1,0),
                  ({'solution Stats Gathering'!I3:I,'solution Stats Gathering'!A3:L}),{2,3,10},0)
      )))),"select * where Col1 is not null")

For the 3rd set use

=QUERY(ArrayFormula(((IFERROR( 
       VLOOKUP(SORT('solution Stats Gathering'!J3:J,1,0),
                  ({'solution Stats Gathering'!J3:J,'solution Stats Gathering'!A3:L}),{2,3,11},0)
      )))),"select * where Col1 is not null")

Please have a look at the SAMPLE SHEET I prepared for you.

If you still want to use the static formulas then, the formula you need for your first line of your first set of data is:

=VLOOKUP(A3,ArrayFormula({stats!$K$3:$K$44,stats!$A$3:$W$44}),2,0)

You can then copy it down and the the right changing the cells/ranges accordingly.

Functions used:

| improve this answer | |
  • Have you tried the dynamic query formulas? Did they work for you? I would love to know. As always, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos May 20 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.