I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

See Debra's site for information
http://www.contextures.com/xlFunctions02.html#Range

Check out this part
http://www.contextures.com/xlFunctions02.html#Trouble

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Lucien" wrote in message ...
I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

there is a chance your values are really the same as your lookup value. If
they are text, you may need to use a trim function.
if they are supoposed to be numeric. One might not beand you will need to
make them the same or you may need to use a round function to make noise
past the display disapear..

"Lucien" wrote:

I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

One reason could be that there isn't actually an exact match for A2 in the
first column of your array ('Apr-MayMaterial'!A2:D735). Even a difference of
a single space character (assuming you're looking up text) could cause this.

From Excel Help: "When searching text values in the first column of
table_array, ensure that the data in the first column of table_array does not
have leading spaces, trailing spaces, inconsistent use of straight ( ' or " )
and curly ( €˜ or €œ) quotation marks, or nonprinting characters. In these
cases, VLOOKUP may give an incorrect or unexpected value."

Does the final "0" in your formula mean "FALSE"? (I'm not familiar with
that version of the syntax) Regardless, if you leave out the final argument,
or replace "0" with "TRUE", VLOOKUP will return the first match (or nearest
match) it finds. If the final argument is "FALSE", VLOOKUP will only return
an exact match--or "N/A" if it can't find one.

"Lucien" wrote:

I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

Hi,

It looks like you are doing the VLOOKUP in a sheet different from the source
sheet. Are you sure that the cell A2 of that sheet (not the source sheet) is
populated with a number, and if yes, does that number match with the contents
of one of the cells in A2:A735 of the source sheet? If there is no match,
the formula will return #N/A.

Regards,
B. R. Ramachandran


"Lucien" wrote:

I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

Thank you for everyone's quick response. My problem was found in the range
data. I had trailing spaces in all the cells. Used TRIM function and
cleaned them all up. Now it works perfect.

Thanks again!!



"B. R.Ramachandran" wrote:

Hi,

It looks like you are doing the VLOOKUP in a sheet different from the source
sheet. Are you sure that the cell A2 of that sheet (not the source sheet) is
populated with a number, and if yes, does that number match with the contents
of one of the cells in A2:A735 of the source sheet? If there is no match,
the formula will return #N/A.

Regards,
B. R. Ramachandran


"Lucien" wrote:

I keep getting a #N/A error when using this formula and I don't know why:

=VLOOKUP(A2,'Apr-MayMaterial'!A2:D735,4,0)

Am I doing something wrong within the formula?

First, make sure the value showing in A2 is actually found in A2:A735 of
your sheet named Apr-MayMaterial. If it's not there you get #N/A.

If it is there make sure it is formatted properly. If the value in A2
is a number and the format of A2:A735 is not then you'll get the #N/A.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=471906

that probably means it didn't find that value in the table array.
Or, it is not exactly written as it is displayed.

For instance, it may be looking for the word JAPAN but the table array
has it as JAPAN_ with a space or something.

Numbers also has ways of playing tricks on you. What looks like zero
because of formatting may actually be 0.0004.

just my 2cents


--
DennisSunga
------------------------------------------------------------------------
DennisSunga's Profile: http://www.excelforum.com/member.php...o&userid=27514
View this thread: http://www.excelforum.com/showthread...hreadid=471906