xlookup does not recognize text unless I retype source

[Dugzilla]

In the first table (above), I have a lookup formula that pulls from the table to the right of it. The table to the right (source) was downloaded from DraftKings (cvs format).

Here's the gotcha: Some pull the correct data, some don't. (Indicated by error code: "BYE or not on main slate") Colts (for example) should have pulled the data. The Eagles (not the band) failed until I retyped Eagles on the DraftKings (source) table. I have tried copy/paste values, changed from general to text and back again on both, but no difference. For the life of me I cannot figure why this is doing what it's doing. AND, I even exported the CVS as an excel sheet, but no difference. ONE LAST THING: It's only the team names... all the players work fine.

formula: =IFERROR(XLOOKUP(S4,DK!$C$3:$C$492,DK!$F$3:$G$492),"BYE or not on main slate!")

I promise if this is an easy solution, I will kick my own butt. Thank you!!!!

 

[NickFru]

Hello - you could possibly try;
=XLOOKUP(IFERROR(S4*1,S4),DK!$C$3:$C$492,DK!$F$3:$G$492,"BYE or not on main slate!")

 

[Peter_SSs]

The table to the right (source) was downloaded from DraftKings

It is common for data drawn from websites to include extraneous non-printing characters.
If you want further investigation, could you upload a small sample file (any sensitive data disguised) to DropBox, Google Drive etc and provide a publicly shared link here so we can take a look an an actual set of data?

 

[Dugzilla]

It is common for data drawn from websites to include extraneous non-printing characters.
If you want further investigation, could you upload a small sample file (any sensitive data disguised) to DropBox, Google Drive etc and provide a publicly shared link here so we can take a look an an actual set of data?

Thank you Peter. I thought it had to be something like that. I will try the CLEAN function (something I just now thought of) and see if that does it. (Unfortunately I don't have something I can upload to, bI will let you know what I figure out.)

 

[Peter_SSs]

I will try the CLEAN function (something I just now thought of) and see if that does it. .... will let you know what I figure out.)

OK, let us know if further help is required.

Unfortunately I don't have something I can upload to

DropBox (& plenty of other places) allow at least some free accounts/uploads

 

[Dugzilla]

Peter, I am a dork. I was troubleshooting the downloaded file when I should have taken a closer look at the original file. The CLEAR function did work but only on a few, so I tried:

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String<br> Dim X As Long, CodesToClean As Variant<br> CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _<br> 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)<br> If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")<br> For X = LBound(CodesToClean) To UBound(CodesToClean)<br> If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")<br> Next<br> CleanTrim = WorksheetFunction.Trim(S)<br>End Function

found at CLEAN() not dependable to remove non-printable characters?

When that didn't work for all of the data, I scratched my head, enough, to where I remembered I had modified my own spreadsheet and it had spaces at the end (not the beginning) of each team name. I had overlooked the easiest fix possible.

The combination of ClearTrim function and checking/fixing my spreadsheet (not the source/downloaded) file was the answer.

Thank you for your help.

 

[Peter_SSs]

Glad you got it sorted. Thanks for letting us know.