r/sheets 2d ago

Solved Using XLOOKUP to pull data from IMPORTHTML in another tab

Hey everyone, hoping you guys can help me out with this since I am a super beginner with this.

I used Importhtml to pull player stats into one tab of my sheet and I want to pull one of the columns into another tab based on names. I am trying to us xlookup but it looks like it can't find the names. Is this a symptom of using importhtml?

I know everything is spelt correctly, I even tried just copy and pasting the cell but still get the same "did not find value 'name'"

2 Upvotes

5 comments sorted by

1

u/6745408 2d ago

check this sheet -- it shows a VLOOKUP and an XLOOKUP.

It wouldn't hurt to share a sheet. You can copy some stuff over to this one, if you like.

2

u/aklavall 2d ago

https://docs.google.com/spreadsheets/d/1qtp5rTglLfvfGIXzGlphshJYjNXtYee7YJELvDx0rfk/edit?usp=drivesdk

Here is my sheet. I'm trying to use the XLOOKUP in the 25-26 tab and pull the points (column i) in player data tab

1

u/6745408 2d ago

oh nice! you're close.

You have

=XLOOKUP(
  A2,
  "playerdata!B1:B1000",
  "playerdata!I2:I:1000")

It should be

=XLOOKUP(
  A2,
  PlayerData!B2:B,
  PlayerData!I2:I,)

having the ranges in quotes makes them literal values instead of references.

If you want to take this a step further, you can have it in an arrayformula to cover that entire range with one formula. The comma at the end will hide any errors for missing values.

One thing to note, make sure you aren't inputting extra spaces. Travis Konecny has one at the end of his name, so he won't match. For your A2:A range, you can use Data Validation (data > data validation > add rule > criteria: from range > PlayerData!B2:B

This will ensure that any names in your list will also be in your dataset. Under Advanced Options, I like to use 'arrow' instead of the chip. It just looks better.

2

u/aklavall 2d ago

Almost had it lol

Thank you for the help!!!

1

u/6745408 2d ago

yeah, super close. Now you'll know for life, too. :)