VLOOKUP: type or paste values
 

Hello! I have an excel sheet with a VLOOKUP function that looks like this:
=VLOOKUP(A27;'Projekt en C27materiaal prijslijst'!A:C;3;0)
and the idea is that by typing in the look-up value the following 4 columns
would be populated by data from the list. What happens is that unless the
initial code is sought, copied and pasted from the source worksheet, all I
get is #N/A. This seems to go contrary to the purpose of the VLOOKUP.
I need to be able to type in the code and not to have to search for it and
to paste it in. What am I doing wrong?
I have considered that perhaps the cell formats of the codes or the fact
that some are numbers and others are numbers and text may make a difference,
but it returns #N/A anyway. Any suggestions?

Just some thoughts ..

I was unable to replicate the formula here in xl97
with the sheetname: 'Projekt en C27materiaal prijslijst'
as it exceeded the limit of 31 chars !

So, not sure whether this (sheetname error?) could have contributed to the
prob. Perhaps a quick re-check and re-input of the sheetname would be a good
idea ..
(note that spaces also count in the 31 chars limit)

If that's not it, you could also try something along the lines of:

=VLOOKUP(TEXT(A27,"000"),'Projekt en C27'!A:C,3,0)

The above assumes the ref table's sheetname is: Projekt en C27 and assumes
the lookup value in A27 is a real number, while the ref values in col A in
sheet: Projekt en C27 are 3 digit "text" numbers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Rochelle" wrote in message
...
Hello! I have an excel sheet with a VLOOKUP function that looks like this:
=VLOOKUP(A27;'Projekt en C27materiaal prijslijst'!A:C;3;0)
and the idea is that by typing in the look-up value the following 4
columns
would be populated by data from the list. What happens is that unless the
initial code is sought, copied and pasted from the source worksheet, all I
get is #N/A. This seems to go contrary to the purpose of the VLOOKUP.
I need to be able to type in the code and not to have to search for it and
to paste it in. What am I doing wrong?
I have considered that perhaps the cell formats of the codes or the fact
that some are numbers and others are numbers and text may make a
difference,
but it returns #N/A anyway. Any suggestions?

=VLOOKUP(TEXT(A27,"000"),'Projekt en C27'!A:C,3,0)

Think you'd need to adapt the sample formula
to suit your language ver, viz.: commas to semicolons ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

... maybe a penny for the thoughts ? <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,
Thanks for your input. I tried shortening the names but it didn't make a
difference - I have Excel from a 2003 package so maybe it allows for more
characters than 97.
I did however read more of the other posts and came upon one with a link to
macros written by David Ritchie for Excel, as well as a wealth of tips on the
VLOOKUP function. I had a suspicion that the VLOOKUP wasn't recognizing the
lookup values and that could be because the values came from different excel
lists which probably had different formats. I tried selecting the column with
the lookup values and changing the cell formats to General (and to Number and
again to Text when that didn't work) but I still got # N/A. Then, under the
heading: "Find out what you have-it may not be what it looks like" at the URL
http://www.mvps.org/dmcritchie/excel/join.htm he spoke about cell
formatting and mentioned that "Changing the format between Text and Number
will have no effect on data already entered, but re-entry of data will be
changed if valid."

So, I had a colleague who was gracious enough to re-enter all of the several
hundred codes into the lookup value colum and now it works!!!

Thanks for your help on this - I have learned soooo much and gathered more
information on this "journey" than I expected! This is most appreciated!

Kind regards,

Rochelle

"Max" wrote:

... maybe a penny for the thoughts ? <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Good to hear you got the problem resolved, Rochelle !
(one way or the other <bg)

Thanks for the detailed feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----