VLOOKUP and MID Functions (Example included)

  • Thread starter Thread starter David M
  • Start date Start date
D

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
..
 
It is a defined name with expandable range....the data
source for the lookup was created from a query...

D
 
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
 
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
 
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
.
Click to expand...

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
 
Back
Top