PC Review


Reply
Thread Tools Rate Thread

Formula not working - vlookup

 
 
John
Guest
Posts: n/a
 
      1st Dec 2011
Hi. I have recently got Excel 2007 at work, and at home I have gone
from 2000 to 2010.

I have a vlookup formula as follows:

=VLOOKUP(E2,Sheet2!E:O,3,0)

It is comparing what is in cell E2 to find a match on sheet 2 in cell
E. If a match is found it is returning the value in cell G.

Unfortunately even though the value is there in cell E2 on sheet 1 and
also sheet 2, it is not returning a positive value and instead I am
getting #N/A! errors. Do you know what the issue could be?

I can't see anything wrong with the formula or data. I have double
checked the data to make sure it is the same format, and that is all
okay with no difference. I was wondering if it could be something else
that is preventing it with this newer version of excel that I need to
enable or disable?

Thanks,
John
 
Reply With Quote
 
 
 
 
Pete_UK
Guest
Posts: n/a
 
      1st Dec 2011
Did it work in Excel 2000 with the same data? Does E2 contain a number
or a text value (like name)? If it is a text value then you might have
extra spaces (which are difficult to see !!), so you can use a formula
like =LEN(E2) in a helper cell to see exactly how many characters
there are. Use the same formula on the cell in Sheet2 that you think
matches. You can get round this using something like:

=VLOOKUP(TRIM(E2),Sheet2!E:O,3,0)

if you have extra spaces in E2, or this:

=VLOOKUP("*"&E2&"*",Sheet2!E:O,3,0)

if you have extra spaces at the beginning or end in your lookup table
(not extra spaces within the text).

If you are using numbers then it might be that one or other is a text
value that just looks like a number (formatting doesn't change that).
In this case you could modify your formula to:

=IFERROR(VLOOKUP(E2*1,Sheet2!E:O,3,0),IFERROR(VLOOKUP(E2&"",Sheet2!E:O,
3,0),"not found"))

Hope this helps.

Pete

On Dec 1, 12:52*am, John <(E-Mail Removed)> wrote:
> Hi. I have recently got Excel 2007 at work, and at home I have gone
> from 2000 to 2010.
>
> I have a vlookup formula as follows:
>
> =VLOOKUP(E2,Sheet2!E:O,3,0)
>
> It is comparing what is in cell E2 to find a match on sheet 2 in cell
> E. If a match is found it is returning the value in cell G.
>
> Unfortunately even though the value is there in cell E2 on sheet 1 and
> also sheet 2, it is not returning a positive value and instead I am
> getting #N/A! errors. Do you know what the issue could be?
>
> I can't see anything wrong with the formula or data. I have double
> checked the data to make sure it is the same format, and that is all
> okay with no difference. I was wondering if it could be something else
> that is preventing it with this newer version of excel that I need to
> enable or disable?
>
> Thanks,
> John


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
If (Vlookup >0) working, but what if Vlookup cell does not exist Steve Microsoft Excel Worksheet Functions 18 18th Nov 2009 08:33 PM
vlookup formula not working with data on separate sheet =?Utf-8?B?YWtlZQ==?= Microsoft Excel Worksheet Functions 18 16th Mar 2006 04:18 PM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM
Vlookup formula in macro not working xlsxlsxlsxls Microsoft Excel Programming 1 30th Oct 2004 06:57 AM
vlookup formula not working..... flick Microsoft Excel Worksheet Functions 2 8th Oct 2003 02:55 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:27 PM.