VLOOKUP and MID Functions (Example included)

[David M]

Does anyone know if VLOOKUP has a limit of how many
columns it will parse? here is an example of my problem...

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

This will work only to the 12th column...when I go to 13
and 14, it brings me back this strange number not what is
in the cel....I put in some test data to make sure and ran
into the same problem....the formula works until it gets
to column 13....any ideas??

Thanx Much
..

 

[Frank Kabel]

Hi
shouldn't be a problem. How is PMDBSTATS defined?

 

[Guest]

It is a defined name with expandable range....the data
source for the lookup was created from a query...

D

 

[Frank Kabel]

Hi
and there's a column 13 defined in this named range?

 

[Guest]

Columns 13, 14, 15 and 16 are in the same range....

D

 

[Frank Kabel]

Hi
looking at the picture you maile me it seems you want to return a date
but only see the integer value. Try formating the resulting cell (with
the VLOOKUP formula) as date and it should work

 

[David M]

Tried that as well ...no go...I am going to create a test
sheet outside of this workbook .....there seems to be no
reason why this should not work unless VLOOKUP has
limits...

d

 

[Ron Rosenfeld]

Does anyone know if VLOOKUP has a limit of how many
columns it will parse? here is an example of my problem...

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

This will work only to the 12th column...when I go to 13
and 14, it brings me back this strange number not what is
in the cel....I put in some test data to make sure and ran
into the same problem....the formula works until it gets
to column 13....any ideas??

Thanx Much
.

There is no limit such as you describe.

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

 

[Frank Kabel]

Hi
see your other post