VLookup Not Updating New Data in Table

**Goldenwing81** · 03-19-2008, 07:43 PM

I have a Vlookup forumla running on a spreadsheet I use at work. I expanded the table today to reflect new changes but the formula is not returning the correct value.

Current Formula
=VLOOKUP(Section2Sum,Section2TableLookup,2)

the Section2Sum is the returned value and due to the new changes this value was not included in the Section2TableLookup. I added the value to the Section2TableLookup and its correct value to be returned in the second column.

when I attempt to test the changes the Section2Sum is coming back as 29.66 which is correct, however it returns a value of 11 which is listed in the Section2TableLookup under a value of 27 not 29.66. What I don't understand is how the formula can be searching for 29.66 and return the listed value for 27.

I have verified that the unique references are looking at the correct ranges... that is to say I did adjust Section2TableLookup so that it looks at the entire table as i had to add 1 new row to the table to input the new value.

I am useing MS Excel 2003... any advise would be greatly appreciated as I need to get this working asap.

GoldenWing

**Ron Coderre** · 03-19-2008, 07:58 PM

You'll need to include the 4th argument of the VLOOKUP function.

Use this:
=VLOOKUP(Section2Sum,Section2TableLookup,2,0)

Instead of this:
=VLOOKUP(Section2Sum,Section2TableLookup,2)

That 4th argument dictates whether VLOOKUP looks for an exact match (0)
or an approximate match (1)...which is the default...and requires that the
lookup values be in ascending order.

Example:
A1: 1
A2: 5
A3: 9
A4: 8

B1: 8....the value to find

If C1: =VLOOKUP(B1,A1:A4,1,1)
it returns 5...because 9 is larger than 8, so the previous item is used.

If C1: =VLOOKUP(B1,A1:A4,1,0)
it returns 8....because 8 exists in the lookup list.

Does that help?

**Goldenwing81** · 03-19-2008, 08:08 PM

I have done as you recomended and changed the formula to the following

=VLOOKUP(Section2Sum,Section2TableLookup,2,0)

however now i only get #N/A which is stating Value Not Avaliable Error. which is crazy because the value is in the table...

**Ron Coderre** · 03-19-2008, 08:15 PM

It sounds like the value *looks like* it's in the first column of the list,
but it really isn't.

Things to look for:
Trailing spaces
Numeric text.....(eg '5)..looks like the number 5, but is actually the *letter* 5.

Here's a quick test.

Copy the lookup value
Past it over the value in the list that you think should match.
...If the formula now reflects the correct value...there was a difference.
<edit><undo>...to restore the value.

Does that help?

**Goldenwing81** · 03-19-2008, 08:40 PM

I have copied the value straight and pasted it into the table.... double check and rechecked that the value is correct with no extra spaces, or inputs... it is absolutly correct in my table... but still won't return the correct value... *sigh* any other ideas?

**Ron Coderre** · 03-19-2008, 08:46 PM

Can you zip a shrinky-dink version of your file, exhibiting the non-matching behavior, and post it? (It'll need to be a zip file...you can't post an .xls file)

Hopefully, we'll be able to spot the issue.