0

I've created an editable link to my Excel workbook here:

https://1drv.ms/x/s!Ai_INzFRkuU2jV_Gcv3wSI0uwwBZ

My objective is to create a column in the "DK" sheet, column "I", that returns the weekly, percentile-ranking of each respective player. The weekly, percentile-ranking of each player is represented in the subsequent sheets. There is one sheet for each position: QB, RB, WR, TE, DST; and, column "I" is the percentile ranking in each sheet.

My formula is:

IF(A2="QB",VLOOKUP(B2,QB!$B$2:$I$27,8,0),IF(DK!A2="RB",VLOOKUP(B2,RB!$B$2:$I$59,8,0),IF(DK!A2="WR",VLOOKUP(B2,WR!$B$2:$I$58,8,0),IF(DK!A2="TE",VLOOKUP(B2,TE!$B$2:$I$32,8,0),IF(DK!A2="DST",VLOOKUP(B2,DST!$B$2:$I$33,8,0),0)))))

The error occurs when I get to "DST"s. For all of them, and only them, this formula returns "N/A". For some reason, the VLookup portion of the formula does not recognize the entries in sheet "DST", column "B", as matching those in sheet "DK" column "B". I suspect this is a formatting issue, but I still can't figure how to resolve it. I've ensured their spelling, checked for spaces, and took the time to properly format each column in identical fashion, all to no avail.

It might be helpful to mention that I used "Text to columns" to parse, what was originally one column, into columns A, B, and C of sheets QB, RB, WR, TE, and DST.

How do I get column "I" in sheet "DK" to return column "I" in sheet "DST", for those observations where Position = DST, without infringing on the rest of the formula?

Let me know if there's anything else I can provide. Thank you for your help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DataProphets
  • 156
  • 3
  • 17
  • I have seen unexpected behavior in ranges used for text-to-columns. Namely, anything pasted into them after the original output was deleted, is itself automatically parsed. I'm on a non-windows box now so I cannot try this out: copy & paste (values only!) the data in the DST sheet several columns off to the right. Then completely delete columns from "A" all the way to the column preceding where your pasted values are. Do it right and your VLookup formulas won't change. That might do the trick. If not, paste-values into a new sheet, and renaming tabs of course, might do it. – klausnrooster Oct 15 '16 at 15:52
  • Well, I tried it via Excel Live on your link. No luck. Next thing to try is Index & Match. Personally I never use V or HLookup. I always use Index & Match. I still recommend the paste-values and column-delete stuff on ALL the ranges where text-to-columns was employed. See [link re: Index & Match] (https://eimagine.com/say-goodbye-to-vlookup-and-hello-to-index-match/) – klausnrooster Oct 15 '16 at 16:22
  • Thank you for all of your help! To pnuts' point, I did have an error in my formula in the excel file. I text-to-columned differently in the online file than I did in my personal file. However, I corrected it and still get "#N/A"s for all DSTs (apparently, I was getting "#ref!" errors before). I looked into index and matching techniques and found the information very helpful! Unfortunately, as it pertains to this example, I'm still getting "#N/A"s for all DSTs. Am I using it properly? (I've updated the spreadsheet as well). – DataProphets Oct 16 '16 at 00:03
  • =IF(A2="QB",VLOOKUP(B2,QB!$B$2:$I$27,8,0),IF(DK!A2="RB",VLOOKUP(B2,RB!$B$2:$I$59,8,0),IF(DK!A2="WR",VLOOKUP(B2,WR!$B$2:$I$58,8,0),IF(DK!A2="TE",VLOOKUP(B2,TE!$B$2:$I$32,8,0),IF(DK!A2="DST",INDEX(DST!$H$2:$H$33,MATCH(DK!B2,DST!$B$2:$B$33,0)),0))))) – DataProphets Oct 16 '16 at 00:03
  • We can all rest easy! I've figured it out lol turns out there was a space after the word, only for the defenses, on sheet "DK". Thanks for your help. – DataProphets Oct 16 '16 at 07:10

0 Answers0