I've used the ImportHTML function to scrape play-by-play information from Basketball Reference.

I am using a Vlookup to create a rating system based on time, point different, and several other factors.

I realized the TIME vlookup isn't reading correctly, but the formula is exactly how it should be (I've tested it, and it works fine when I type in the info manually). It only doesn't work on the time that is coming from my ImportHTML.

I can't get the format to match - I've tried every format option on both my vlookup & the data, and tried forcing it with the TEXT function (might of not done that correctly).

I'm at an absolute stand-still at this point -- has anyone experienced anything similar?

Other ways to force a format change on imported data? or another way to correctly pull the values from a range of times? I've tried the Lookup function as well.

Demo Link:


For Reference:


upper lower bounds


  • It is because your time cells are not formed as numbers. There are different remedies for that. But instead of trying to guess which is the best one, please read how to and share a test sheet so as you can be easier helped. – marikamitsos May 30 '20 at 18:49
  • Thanks @marikamitsos - I couldn't seem to get the cells to switch format no matter what I tried - I'm sure there is a way to do so though. I updated my question to include a demo link; really appreciate it - this is driving me nuts! – Anthony Madle Jun 1 '20 at 1:10
  • Sorry, I am kind of lost in your sheet. Please, in a new tab, write only some data (not 500 rows), the vlookup formula you try and the expected results. – marikamitsos Jun 1 '20 at 2:14
  • Hey @marikamitsos my apologies - I tried to throw as much info on there as possible, but it is cleaner now & should be easier to follow. The issue is on the "Rating" tab; "Raw Bball Info" is only there to show you how I'm importing the times (Column A) – Anthony Madle Jun 1 '20 at 22:20
  • Sorry but you still have 500+ rows and still cannot understand which is the "My issue is getting this vlookup to work..." or where/what are your expected results. – marikamitsos Jun 1 '20 at 22:26

Your Answer

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

Browse other questions tagged or ask your own question.