VLOOKUP Question

I feel stupid but I cant figure out what is wrong with my forumla. It will
only return #N/A.

Here is the formula:

=(VLOOKUP(J2,List!$A$1:$G$404,6,FALSE))

Where "List" is another tab on the same workbook and 6 is the column that I
want ito lookup.

You don't need those outside ()'s, but that won't matter.

=VLOOKUP(J2,List!$A$1:$G$404,6,FALSE)

My guess is that you don't have an exact match in List!A1:A404 for the value in
J2.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

OverMyHead wrote:

I feel stupid but I cant figure out what is wrong with my forumla. It will
only return #N/A.

Here is the formula:

=(VLOOKUP(J2,List!$A$1:$G$404,6,FALSE))

Where "List" is another tab on the same workbook and 6 is the column that I
want ito lookup.

--

Dave Peterson

Hello,

Sometimes the vlookup fails because what you are looking for is not the same
as what you have given.

It is one of the classic issues in computing when 1 does not equal 1.

You are probably trying to compare a number with a text representation of 1.

One of the cells is probably formatted as text or it has quotes or a single
quote on it.

You can solve this by converting the search to text as in =TEXT(1,"0").

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"OverMyHead" wrote:

I feel stupid but I cant figure out what is wrong with my forumla. It will
only return #N/A.

Here is the formula:

=(VLOOKUP(J2,List!$A$1:$G$404,6,FALSE))

Where "List" is another tab on the same workbook and 6 is the column that I
want ito lookup.

Nothing wrong with your vlookup formula, other than the extraneous outer
parens. The problem lies in the data that you're trying to match. It looks
like it should match but it doesn't. Could be either text numbers vs real
numbers issue and/or the presence of extra white spaces for text matches
which is throwing things off.

With your data as-is (ie w/o you having to clean/modify the source
data/lookup values), you could try this index/match for more robust results,
normal ENTER to confirm will do:
=IF(LEN(J2)=0,"",INDEX(List!F$2:F$404,MATCH(TRIM(J 2&""),INDEX(TRIM(List!A$2:A$404&""),),0)))
Copy down. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"OverMyHead" wrote:
I feel stupid but I cant figure out what is wrong with my forumla. It will
only return #N/A.

Here is the formula:

=(VLOOKUP(J2,List!$A$1:$G$404,6,FALSE))

Where "List" is another tab on the same workbook and 6 is the column that I
want ito lookup.