software

Hi

Can anyone help with this problem.

I am using a VLOOKUP to pull information from a worksheet called quot;Feb Loan
06quot; This is the exact formula as used in my worksheet:
=IF(ISERROR(VLOOKUP($A3,'Feb Loan
06'!$A$2:$D$473,4,FALSE)),0,VLOOKIP($A3,'Feb Loan 06'!$A$2:$D$473,4,FALSE))

I use this formula very regularly but this is the first time the formula
does not pull in the correct data.

The data it is pulling in should show either the value 0.00 or any other
number. There are other numbers larger that 0.00 in the column I am pulling
from, but for some reason, the only figure getting pulled through is 0.00,
even if the client should have a figure of 3000.00 for example.

Any ideas as to what I may be doing wrong.

Thanks in advance

Hi,

Assuming the obvious has been checked; i.e the data is actually in the
search table and the references and returns column number are valid.

Check the formatting of the search object and the cells being searched. I
would expect the prime cause to be that you are looking for a number in a
text field or vice versa. You are getting zeros because your error trap is
correcting either ISNA# or VALUE# errors to 0.
If your search area is a data dump also watch for trailing or leading spaces
convert what looks like a number to text.

The fact that the formula has been OK before hints to me that the data
format has changed.

hth

Giz

quot;malycomquot; wrote:

gt; Hi
gt;
gt; Can anyone help with this problem.
gt;
gt; I am using a VLOOKUP to pull information from a worksheet called quot;Feb Loan
gt; 06quot; This is the exact formula as used in my worksheet:
gt; =IF(ISERROR(VLOOKUP($A3,'Feb Loan
gt; 06'!$A$2:$D$473,4,FALSE)),0,VLOOKIP($A3,'Feb Loan 06'!$A$2:$D$473,4,FALSE))
gt;
gt; I use this formula very regularly but this is the first time the formula
gt; does not pull in the correct data.
gt;
gt; The data it is pulling in should show either the value 0.00 or any other
gt; number. There are other numbers larger that 0.00 in the column I am pulling
gt; from, but for some reason, the only figure getting pulled through is 0.00,
gt; even if the client should have a figure of 3000.00 for example.
gt;
gt; Any ideas as to what I may be doing wrong.
gt;
gt; Thanks in advance

Thanks for the quick reply Gizmo63, however, the cells that I am pulling from
and to are formatted exactly the same way. The particular worksheet I am
working on has several VLOOKUPS in it.

I have even copied one of the other VLOOKUPS exactly but the same thing is
happening. Just returning 0.00 even though the column I have copied the
VLOOKUP from is showing the correct data that has been pulled from another
worksheet.

I hope this makes sense.

Is there a maximum number of VLOOKUPS you can have in one worksheet? I am
sure I have worked on spreadsheets with more VLOOKUPS that the one I am
working on now.

Regards

quot;Gizmo63quot; wrote:

gt; Hi,
gt;
gt; Assuming the obvious has been checked; i.e the data is actually in the
gt; search table and the references and returns column number are valid.
gt;
gt; Check the formatting of the search object and the cells being searched. I
gt; would expect the prime cause to be that you are looking for a number in a
gt; text field or vice versa. You are getting zeros because your error trap is
gt; correcting either ISNA# or VALUE# errors to 0.
gt; If your search area is a data dump also watch for trailing or leading spaces
gt; convert what looks like a number to text.
gt;
gt; The fact that the formula has been OK before hints to me that the data
gt; format has changed.
gt;
gt; hth
gt;
gt; Giz
gt;
gt; quot;malycomquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; Can anyone help with this problem.
gt; gt;
gt; gt; I am using a VLOOKUP to pull information from a worksheet called quot;Feb Loan
gt; gt; 06quot; This is the exact formula as used in my worksheet:
gt; gt; =IF(ISERROR(VLOOKUP($A3,'Feb Loan
gt; gt; 06'!$A$2:$D$473,4,FALSE)),0,VLOOKIP($A3,'Feb Loan 06'!$A$2:$D$473,4,FALSE))
gt; gt;
gt; gt; I use this formula very regularly but this is the first time the formula
gt; gt; does not pull in the correct data.
gt; gt;
gt; gt; The data it is pulling in should show either the value 0.00 or any other
gt; gt; number. There are other numbers larger that 0.00 in the column I am pulling
gt; gt; from, but for some reason, the only figure getting pulled through is 0.00,
gt; gt; even if the client should have a figure of 3000.00 for example.
gt; gt;
gt; gt; Any ideas as to what I may be doing wrong.
gt; gt;
gt; gt; Thanks in advance

Problem solved

For some bizzare reason, certain columns in my worksheet had Automatic
Calculations turned off.

For anyone else who may have a similar problem, this can be found by going
to Tools gt; Options gt; Calulations Tab gt; makes sure that Automatic is selected
in the Caculation section.

Hope this helps anyone else who may experience this problem.

Thanks Gizmo for you quick reply anyway.

Regards

quot;malycomquot; wrote:

gt; Hi
gt;
gt; Can anyone help with this problem.
gt;
gt; I am using a VLOOKUP to pull information from a worksheet called quot;Feb Loan
gt; 06quot; This is the exact formula as used in my worksheet:
gt; =IF(ISERROR(VLOOKUP($A3,'Feb Loan
gt; 06'!$A$2:$D$473,4,FALSE)),0,VLOOKIP($A3,'Feb Loan 06'!$A$2:$D$473,4,FALSE))
gt;
gt; I use this formula very regularly but this is the first time the formula
gt; does not pull in the correct data.
gt;
gt; The data it is pulling in should show either the value 0.00 or any other
gt; number. There are other numbers larger that 0.00 in the column I am pulling
gt; from, but for some reason, the only figure getting pulled through is 0.00,
gt; even if the client should have a figure of 3000.00 for example.
gt;
gt; Any ideas as to what I may be doing wrong.
gt;
gt; Thanks in advance

Sometimes, it's better to copy from the formula bar and paste into the message.

Are you sure you included enough rows?

If there's nothing below your table, maybe:

=IF(ISERROR(VLOOKUP($A3,'Feb Loan 06'!$A:$D,4,FALSE)),0,
VLOOKuP($A3,'Feb Loan 06'!$A:$D,4,FALSE))

And if $a3 contains only digits, maybe the numbers are text in one location and
really numbers in the other (or maybe you have leading/trailing/embedded
spaces)) or maybe it's just a typo.

Debra Dalgleish has some troubleshooting tips at:
contextures.com/xlFunctions02.html#Trouble

malycom wrote:
gt;
gt; Hi
gt;
gt; Can anyone help with this problem.
gt;
gt; I am using a VLOOKUP to pull information from a worksheet called quot;Feb Loan
gt; 06quot; This is the exact formula as used in my worksheet:
gt; =IF(ISERROR(VLOOKUP($A3,'Feb Loan
gt; 06'!$A$2:$D$473,4,FALSE)),0,VLOOKIP($A3,'Feb Loan 06'!$A$2:$D$473,4,FALSE))
gt;
gt; I use this formula very regularly but this is the first time the formula
gt; does not pull in the correct data.
gt;
gt; The data it is pulling in should show either the value 0.00 or any other
gt; number. There are other numbers larger that 0.00 in the column I am pulling
gt; from, but for some reason, the only figure getting pulled through is 0.00,
gt; even if the client should have a figure of 3000.00 for example.
gt;
gt; Any ideas as to what I may be doing wrong.
gt;
gt; Thanks in advance

--

Dave Peterson