Formula Has Stopped Working

[David M]

WOW!! I am completely stumped!! Here is the formula in
question:

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",MID(VLOOKUP
(L4,PMDBSTATS,12,FALSE),1,10))

Now this works until I try to parse columns beyond #13. I
have tried the following:

1. copied to another cell within the named range...result:
returns an integer:

2. copied the exact formula into another cell..result:
returns duplicate information(thats good)...

3. okay created a test sheet with two columns, defined a
name copied the formula to an empty cell, result: returned
and integer....

4. lastly, I manually stepped through the formula,manually
choose the columns to parse, told excel to retun the data
in the 3rd column...result: returned an integer....

So it appears for some reason it has stopped working...if
all this sounds confusing?? Can anyone offer an idea???

Thanx so much..

David

 

[Guest]

check what the named range of PMDBSTATS is...it maybe only
set to 12 columns?

 

[MattShoreson]

Check the dimensions of the named range in the lookup formula

 

[David M]

Thats the first thing I did...that is not the issue..

 

[Frank Kabel]

Hi David
I think I already gave you a possible answer that you got
the serial of a date returned.
Please give the following information:
- your expected result from VLOOKUP
- the returned result

 

[David M]

In this instance, the name rage is NOT the issue...all the
data IS contained in the named range.....I need someone to
look beyond this as being the issue...

D

 

[David M]

YES!! You are correct I got the serial number instaed of
the DATE...I am looking for the DATE....now when I try to
format the cell to a DATE ...it still give the serial
number.....I have tested this out in several ways with no
luck....

 

[Frank Kabel]

Hi
if you get a date serail value formating should do. If you
like. email me your file and I'll have a look at it

How did you try to format the cell?

 

[Ron Rosenfeld]

WOW!! I am completely stumped!! Here is the formula in
question:

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",MID(VLOOKUP
(L4,PMDBSTATS,12,FALSE),1,10))

Now this works until I try to parse columns beyond #13. I
have tried the following:

1. copied to another cell within the named range...result:
returns an integer:

2. copied the exact formula into another cell..result:
returns duplicate information(thats good)...

3. okay created a test sheet with two columns, defined a
name copied the formula to an empty cell, result: returned
and integer....

4. lastly, I manually stepped through the formula,manually
choose the columns to parse, told excel to retun the data
in the 3rd column...result: returned an integer....

What is the "strange number" being returned?
Are you expecting a date?

If that is the case, then it is likely that the number being returned
represents that date. However, since the MID function returns a string (text),
formatting the cell as a date will have no effect on the display.

Try putting a double unary in front of the MID function (to convert the
string to a number), and format the cell as a date.

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,13,FALSE)),"",--MID(VLOOKUP
(L4,PMDBSTATS,13,FALSE),1,10))


--ron

 

[David M]

Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....

;(

 

[Ron Rosenfeld]

Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....

And what happened when you formatted the cell as a date, as I suggested?


--ron

 

[Ron Rosenfeld]

Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....

As I mentioned, you need to ALSO format the cell as a date. Just putting the
double unary only converts the result to a number. The number will then get
displayed depending on the format of the cell.


--ron

 

[Frank Kabel]

Hi
after looking again at your formula: Remove the MID function:
=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",VLOOKUP(L4,PMDBSTATS,12,
FALSE))

As you want to return a date: no need for MID. and format this cell as
date and it should work

 

[Frank Kabel]

Hi Ron
even better would be to remove the MID function call as a date is
expected

 

[Ron Rosenfeld]

Hi Ron
even better would be to remove the MID function call as a date is
expected

Not having seen his worksheet, I thought perhaps the contents of the cell
included some trailing text after the date. Although I would have used a LEFT
function were that the case.

But if all that is in the cell is a date, then the MID is superfluous, possibly
causing part of the problem. Or not if the contents of the cell is TEXT.


--ron