why doesn't vlookup function work?

[lalaexcel]

I have been using the Vlookup function, it works well in certain aspects
but not all. Sometimes it does not work but returns incorrect data.
Recently I am trying to have my works computerized. I have four
figures (for example 1234 represent wood material and 5678 for metal)
and another four figures (1100 for painting and 1300 for cutting)
represent work done. If I have an item of painting on wood material ,
I would quote 12341100. My problem is an incorrect description and
price would appear when I use such lengthy code to vlookup! If I only
name the items with 1 to 10000 then the trouble disappears. Would
someone please help me?

 

[Roger Govier]

Hi

There is no inherent reason why Vlookup will not work in your scenario.
My suspicion would be that your products are using 8 digit numbers,
12341100, but the values you are trying to look up are text values of
12341100, especially if they are being produced as a result of adding
material and work together with concatenation 1234&1100.

Try forcing the values to be numeric by putting a double unary minus in
front of the value being looked up
=VLOOKUP(--(value being looked up),lookup table,offset,false)

If this doesn't help, post back with more detail including Sheet names
and locations of data, and formulae being used.

--
Regards

Roger Govier


"lalaexcel" <[email protected]>
wrote in message
news:[email protected]...

 

[Bucky]

I have been using the Vlookup function, it works well in certain aspects
but not all. Sometimes it does not work but returns incorrect data.

Is the last parameter of the function set to "false"?

 

[BenjieLop]

Yes, the last argument is "false" ...

You can set it to 0 (zero) if you do not like to use "false" as it
means the same thing as well.

Regards.

 

[Niek Otten]

<Yes, the last argument is "false" ... >

Unlikely. In that case #NA would be returned, not "incorrect data".

Anyway, without, formula, search argument, values in the list, nature of the
values (text, numbers, numbers stored as text), list sorted yes/no, it is
not feasible to even guess what the problem is.

 

[lalaexcel]

Hi Roger, Bucky, Benjelop and Niek

Thank you all for your prompt and effective advice. The problem i
solved.

Could you kindly tell what that "--" .and. "false" represent?
It is quite amazing, the problem persisted without the presence o
either of them. Please tell me meaning what these two wonderfu
symbol/string .

Once again thank you for your great help!

Regards
lalaexce

 

[Roger Govier]

Hi

The False or 0 needs to be used as the 4th argument in the Vlookup
formula, if the data being looked up is not in a sorted order.
Without it, once a value greater than the value being looked up has been
reached, Vlookup stops looking any further.

The double unary minus or -- is used to coerce either True and False
values to 1 or 0, or, in this case, if the number has been produced by
concatenation of two numbers, it coerces the text value into a numeric
value.

--
Regards

Roger Govier


"lalaexcel" <[email protected]>
wrote in message
news:[email protected]...

 

[lalaexcel]

Hi Roger

Thank you so much . Your answer is precious and clear.

Before taking excel, I was using dbase III plus to store data and sort.
I used to type in name of chemicals to retrieve their prices and
descriptions. Has excel similar function to get data?


Regards
lalaexcel

 

[Chip Pearson]

You probably need to use the VLOOKUP function. Put your chemical
names in column A, and their related properties in adjacent
columns, say B:K. Then use a formula like

=VLOOKUP(Chem_Name,$A$1:$K$255,Prop_Column,FALSE)

where Chem_Name is the chemical name (either a literal string in
quotes or a cell reference) and Prop_Column is the column number
of the property you want to retrieve. See help on VLOOKUP for
more info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"lalaexcel"
message
news:[email protected]...

 

[lalaexcel]

Hi Pearson

Thank you the problem is now solved.

Regards
lalaexcel