VLookup Table Pulling Up the Wrong Values

**mrvp** · 01-01-2012, 04:53 PM

Hi,

I've done quite a few vlookup tables without issues but every now and again I have issues that I just don't get why it is going wrong (e.g. I am usually using similar formulas and occasionally they just seem to go wrong or pull the wrong value for what ever they are looking up). On my current sheet I am having the following:

The formula (in the case of the third line of the lookups where the errors seem to start) in cell J24 is:

=$H$1+VLOOKUP(C24,LookUp1,2)-VLOOKUP(G24,LookUp1,2)

In terms of the formula

$H$1 - is a constant to be applied to all cells (in this case homefield advantage)

C24 in this case is the Home Team - Leicester
G24 in this case is the Away Team - Exeter

LookUp1 is the name of the table on cells E5:F17, with the E column being the Team, and the F column being the rating (so I am asking the formula to read column 1 to match the team effectively and then put the rating from column 2 into the equation).

The problem in the case of the above is the C24 bracket does pull up the Leicester rating, however the G24 bracket doesn't pull up the Exeter rating (it pulls a "Value Not Available" - even though it is in the same table as the Leicester rating).

We also have issues later on in the table with any Northampton Lookup bracket pulling up the value for Newcastle which is in the line above it (and even though Newcastle isn't in the relevant lookup line). I just had a gut feeling the value was wrong and checked the edit formula option to check the values being calculated. Is there any way of doing an automatic calculation check to check for the wrong values being pulled up (as I don't want to have to manually check about 80 lines of calculations).

Thanks in advance for any assistance,

**Osvaldo Palmeiro** · 01-01-2012, 05:13 PM

Hi.
have you tried?

=$H$1+VLOOKUP(C24,LookUp1,2,0)-VLOOKUP(G24,LookUp1,2,0)

**martindwilson** · 01-01-2012, 05:15 PM

=$H$1+VLOOKUP(C24,LookUp1,2,false)-VLOOKUP(G24,LookUp1,2,false)

**mrvp** · 01-01-2012, 05:38 PM

Hi Martin and Osvaldo,

I've tried both of the formulas suggested and now everything returns a "Value Not Available" (where as with the original formula about 50% of the cells added up correctly). As per my details on the left I'm using Excel 2004 for Mac (if this helps).

Does the fact that I grabbed some of the data off of the internet add to/possibly cause the problem that I'm having (e.g. I copied it, and then pasted it as Unicode text into the worksheet - as the formatting dropped in helpfully)? Re: the 50% comment above, as the problem only seems to be being caused by about 3 or 4 teams out of 12 so I am assuming that this is why at least 50% calculates correctly (e.g. as Exeter returns a "Value Not Available" - any line involving Exeter - 11 or 12 games out of about 80 doesn't calculate).

**martindwilson** · 01-01-2012, 05:56 PM

false or 0 returns exact matches only,maybe your data has spaces in or something .post a sample showing a bit that doesn't work

**Osvaldo Palmeiro** · 01-01-2012, 06:04 PM

I suggest you check for improper space at the names (common to data downloaded from the net).
For example select the cell that holds 'Exeter', press F2 and make sure the flashing cursor will be positioned close to the first and then to the last letter of that name hiting alternately Home and End

Another check is to count the number of characters of the name. For example, if 'Exeter' is in cell 'D10', in any cell put = LEN (D10). It should return 6.

**mrvp** · 01-01-2012, 06:15 PM

Hi Guys,

Thanks for the advice. I expected that this may be an issue and that the original data hadn't gone in cleanly (so to speak). I will try some of Osvaldo's suggestions and let you know how they go tomorrow (early start for something else). If 0 or False return exact matches and by doing that it threw all of the values (re: post no.4 above), it sounds like the whole original set of data is slightly off (e.g. there are possibly spaces in it). Could I use TRIM to take these out? I have tried to paste a sample of the data set below - but I don't know how this will come out (I may edit it out later if the formatting is off).

FIXED AVERAGE 21.1119403 HOME EDGE 3.9836464
SUM OF SSE #N/A
AVERAGE 21.1119403

TEAM RATING
Bath -0.04397234
Exeter -0.25040071
Gloucester 5.34954622
Harlequins 9.90412044
Leicester -3.18640770
London Irish -0.68410384
London Wasps -9.83862847
Newcastle -1.98195521
Northampton 2.35954504
Sale 2.21102029
Saracens -2.73956525
Worcester -1.09919847
AVERAGE 0.00000000

AVERAGE 22.97014925 19.25373134
DATE SCHEDULE HOME TEAM HOME SCORE AT AWAY SCORE AWAY TEAM MOV PREDICTED MOV ERROR SQUARED ERROR
1 Northampton 26 AT 24 Gloucester 2 6.59359215 -4.59359215 21.10108881
1 London Irish 24 AT 29 Harlequins -5 -2.05000366 -2.94999634 8.70247842
1 Leicester 28 AT 30 Exeter -2 #N/A #N/A #N/A
1 Worcester 17 AT 12 Sale 5 0.52490289 4.47509711 20.02649414
1 Saracens 15 AT 20 London Wasps -5 1.92818498 -6.92818498 47.99974716
1 Newcastle 9 AT 22 Bath -13 #N/A #N/A #N/A
2 Sale 30 AT 29 London Irish 1 9.38107439 -8.38107439 70.24240792
2 Harlequins 26 AT 13 Northampton 13 15.86972204 -2.86972204 8.23530460

**Marcol** · 01-01-2012, 06:24 PM

Use this to check the cells or table that come from the internet.

=IF(OR(AND(CODE(RIGHT(A2,1))>=65,CODE(RIGHT(A2,1))<=90),AND(CODE(RIGHT(A2,1))>=97,CODE(RIGHT(A2,1))<=122)),"",CODE(RIGHT(A2,1)))

If this returns values then you will know what characters you need to clean.
e.g.

=SUBSTITUTE(CLEAN(TRIM(G24)),CHAR(160),"")

If the returned values are all 32 then this will do

=TRIM(G24)

**mrvp** · 01-01-2012, 06:30 PM

It's definitely a length thing - (sorry I haven't figured out how to paste in from Excel cleanly yet) =LEN(E6) returned 5 for the top line (and you can see it is one character longer than the team name for all of the other teams):

TEAM RATING
Bath -0.04397234 5
Exeter -0.25040071 7
Gloucester 5.34954622 11
Harlequins 9.90412044 11
Leicester -3.18640770 10
London Irish -0.68410384 13
London Wasps -9.83862847 13
Newcastle -1.98195521 10
Northampton 2.35954504 12
Sale 2.21102029 5
Saracens -2.73956525 9
Worcester -1.09919847 10
AVERAGE 0.00000000

Thanks for the advice Marcol - I will try this tomorrow and let everyone know how it goes. Incidentally Happy New Year to all.

**mrvp** · 01-02-2012, 01:00 PM

Just confirming that this has been solved - where I posted without reading Marcol's edit last night (very close together), I didn't see the point about the 32, so I just added the second formula in for all text from my original data sets (just created two dummy columns on my data worksheet), and then added a zero to my vlookup formula on my analysis worksheet to force the exact match. Everything now works fine and I'll check data that I'm bringing in a lot more thoroughly (bizarrely it just seems that it was the home team that were off - as they all returned a 32 - the away teams were fine, but as I'd constructed my table from team names in the home team column the table wouldn't match either).

Thanks to everyone for their advice.