Excel Lookup Help

[benjy]

I'm trying to use vlookup (or some other excel function) to nab data on starters from an excel worksheet I've compiled. After running a query from Pinny my hitting data "looksup" just fine, but due to Pinny using initials and my worksheet using full given names (e.g. B. Zito vs. Barry Zito) I can't get it to work.

Any help would be appreciated. Thanks.

[TomG]

You need your lookups to match exactly (the format of the cell must match as well). So create another column next to Barry Zito and use a formula like =LEFT(A3,1) & "." & MID(A3, FIND(" ", A3, 1), 99). That will take Barry Zito and turn it into B. Zito (where A3 is the pitcher's name)

[uva3021]

you can also use

=REPLACE(A1,2,FIND(" ",A1)-1,". ")

The VLOOKUP function requires text to be in alphabetical order for most accurate lookup results

With extracting data from Pinny, I would suggest to make a macro that on run copies the relevant cells into another sheet then you can manipulate the data from there so running a query from pinny won't disrupt the data

[benjy]

Thanks so much guys!

[skrtelfan]

How do you get around errors that result when two pitchers have the same first initial and last name, i.e. Jim Johnson on the Orioles and Josh Johnson on the Marlins, I guess you need to add the team's name into the lookup function somehow?

[benjy]

How do you get around errors that result when two pitchers have the same first initial and last name, i.e. Jim Johnson on the Orioles and Josh Johnson on the Marlins, I guess you need to add the team's name into the lookup function somehow?

There will have to be some customization. Pinny isn't entirely consistent with a single initial for first name, likely to avoid the confusion you've noted. I'll first check for duplicates (like you're example) and then alter the name entries to match Pinny as discrepencies come up. It's a little bit of work but still sooooo much easier than finding, copying and pasting each and every starter each day.

[mminkovski]

you could consolidate pitcher name and team name before running vlookup

[benjy]

you could consolidate pitcher name and team name before running vlookup

Good idea but pitchers have changed teams and I like that my database shows where they have played (and posted their stats) in the past.