Help with Vlookup in excel

[J_Barnes]

I was using the tips on how to setup a Vlookup excel sheet for projections.

Everything was working just fine... and then I went to update the info. Here is what i did.
I downloaded the new projections and saved them as an excel file. I combined the name cells into one cell, copied and pasted it as value only (just like I did the first time) to get the players whole name in one call. I deleted all the other columns to the left of my newest name column.

I highlighted and named the data the same as my other data name.

I copied this data set and pasted on top of the old data set in my draft guide spreadsheet.

I keep getting a reference error stating that the data set is not valid.

I tried a few different things like rename the original sheet for the projection data to something else... then make a whole new sheet following the instructions.... then I copied in the whole sheet, then named the data to match what I had in the Vlookup formula... not dice.

I even tried using a completely different naming convention and changing it in the formula, still no dice.

So, my question is.. when you want to update the projection data, what is the best method for doing this using the Vlookup method that is posted??

THanks in advance.

Jeff Barnes

[Bill Macey]

This is a pretty tough one to diagnose without the file itself. Can you post your formula and the error message that's showing up? (i.e., are you getting #N/A or a different result)

[J_Barnes]

Bill-

Thanks for the quick reply.

I tore it down and started from scratch..... meaning I took out all the formula stuff and re entered the formula stuff with all pages updated and in place.

This got rid of the REF error I was getting, but now I get a #N/A error stating the players name isnt there. I check and of course the name is there.

Here comes the crazy part... If I manually type the name, exactly as it was, the formula works and I get the data.

I made sure that the name was in fact correct and when I do this for all the other available players, the data shows up and all the #N/A's are gone.

Any thoughts on why? Especially since the data was there with the names exactly the same way...

Thanks in advance.

[usualsuspects]

I'm having similar issues trying to update the projections using a web query.

The problem may be that the internal reference to column numbers in the vlookup formula does not update when you add or delete rows in the reference area. So unless you paste your new projection set exactly over your old projection set, your vlookups will be pointing at cells that have moved or are gone. I also tried copying and pasting vlookups to new sheets, those formulas did update and still pointed at the old sheets, meaning the solution was to rewrite the formulas from scratch on the new sheets.

[Bill Macey]

One potential explanation - the Baseball HQ names often come with an extra space at the end of the name, which will throw off vlookup formulas. For example, a common format is:

<Last><Comma><Space><First><Space>

if you use a vlookup to get <Last><Comma><Space><First>, it will not match.

[PD@HQ]

Bill is right-- the problem is often unseen spaces in either the vlookup source or the reference table. You can fix this by highlighting the whole name list in each table and getting rid of spaces using the Find-and-Replace function to replace <space> with nothing.

[RobR]

Excel's TRIM() function may prove useful.