We help IT Professionals succeed at work.

# 'VLOOKUP' not updating any new information added.

on
892 Views
I am working on a workbook with two worksheets at present (One with cost prices and the second with formulas).
I am using vlookup from the second sheet to to find the part and price on sheet one (=VLOOKUP(G13,Sections,12) for example.
This works fine for all information entered before i created the Table Array 'Sections'. Now i am adding more parts but for some reason the formula cannot see the new parts and prices entered and is just leaving me with an empty cell instead of the amount it is suppose to show. I have tried refreshing  the worksheet and still nothing.

Comment
Watch Question

## View Solution Only

Commented:
Hi Ian192,

Are you sure that "Sections" is referring to the entire table?
Any chance you could upload the workbook for us to take a peek? (try www.ee-stuff.com)

Cheers!

Commented:
https://filedb.experts-exchange.com/incoming/ee-stuff/2120-Quotation-Buildup--Ian-Only-.zip

Example in workbook sheet CS013 was added before table array was added and is working as you can see.
Parts added after are CS163, CS206, GK036, WP02, WP03, NC033XSLF

Commented:
Hi,
It seems the range named "sections" is an absolute range and not a dynamic one. Try using offset to create a dynamic range as follows.
Suppose yours data range starts in A1 on Sheet1
Then in
Insert->Name->Define
Use in
--Names in Workbook-----Sections
--Refers to-----------------=offset(Sheet1!\$A\$1,,,counta(Sheet1!\$A:\$A),counta(Sheet1!\$1:\$1))

Now whenever you add some new data starting from Column A, the range will automatically expand and range "Sections" will include them automatically.

HTH
Regards
Kanwaljit
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
The formula in column H needs to be:

=VLOOKUP(G13,Sections,12,FALSE)

as the price list is not necessarily sorted in order.

Commented:

Commented:
seems reloaded the page centuries ago......:(

Commented:
Hi Patrick,
You are right, the formula does works, but I couldn't see any duplicate values there. Also there can't be any largest or smallest values in case of lookup value column being a text column (like column A here in "Prices" sheet). What is the reason ?
Kanwaljit

Commented:
In other words, how there can be an approximate match in case of text values....?

Commented:
and Ian, it is not a good thing to enter the data by leaving some blank rows and columns in between. That is very likely to invite trouble...

Commented:
kanwal_no1,

FALSE is confusing in that is copes with unsorted lists as well as exact matches - at the same time. Of course with a text match there are no approximate matches but the list will probably not be sorted so I put in FALSE to cover that eventuality.

Patrick

Commented:
Thanks Patrick, but I still couldn't see how the vlookup can return a zero despite the lookup value being present row. There is something strange there. I entered CS206 in cell A3 and still no change in results. What is the mystry ?
Kanwaljit

Commented:
Excellant cheers guys =Prices!\$A\$3:\$L\$65536 worked for me :)... i am a total novice but normally get by  with the basics...

Commented:
Ian192 - Thanks for the grade. - Patrick

Commented:
Hi Patrick,
Ian might have got the answer, but I still lost. Will you please pull me out......:)
Kanwaljit

Commented:
Kanwaljit,

Let me have another look.

Patrick

Commented:
Kanwaljit,

It was because the range named 'Sections' needed to cover the range Prices!\$A\$3:\$L\$65536. But then I think you knew that. :))

Patrick
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.