VLOOKUP/MID Issue

Hi all,

I'm trying to run the below formula, but keep getting an "N/A", even when I've checked there is something that should be returned:

=VLOOKUP(MID(A5,3,5),'Capacity Requirements.xls'!data,4,FALSE)

I've done the usual checks - A5 relates to the first column in 'Capacity Requirements.xls, etc - but for some reason it still doesn't want to work. I'm wondering if there's some issue mixing MID with VLOOKUP, although I have used the similar LEFT and RIGHT worksheet functions in the past with no problems.

Any ideas what's wrong?

TIA,

SamuelT

Originally Posted by SamuelT

Hi all,

I'm trying to run the below formula, but keep getting an "N/A", even when I've checked there is something that should be returned:

=VLOOKUP(MID(A5,3,5),'Capacity Requirements.xls'!data,4,FALSE)

I've done the usual checks - A5 relates to the first column in 'Capacity Requirements.xls, etc - but for some reason it still doesn't want to work. I'm wondering if there's some issue mixing MID with VLOOKUP, although I have used the similar LEFT and RIGHT worksheet functions in the past with no problems.

Any ideas what's wrong?

TIA,

SamuelT

A5 is the cell A5 on the sheet with the formula, and hopefully has a Len() of 8 or more.

Is 'Data' a named range?

---

Hi Bryan,

Thanks for the quick response. Yes 'Data' is the named range, (I've also tried simply selecting the range - e.g. $A$1:$J$120) but still to no joy.

Also, yes the length of the string is 11 characters, and A5 is A5 on the sheet I'm putting the formula on.

As an experiment, I also pasted the entire 11 character string into one of the cells and removed the MID function, and it worked fine!

SamuelT

Hi,

Just to let you know that I've found a solution that doesn't use the MID function and that seems to be working fine (it's a bit long winded, but deadlines is deadlines!)

Would be interested if anyone can suggest why the formula didn't work, but don't worry yourselves too much.

Thanks to Bryan for his input.

SamuelT

Originally Posted by SamuelT

Hi,

Just to let you know that I've found a solution that doesn't use the MID function and that seems to be working fine (it's a bit long winded, but deadlines is deadlines!)

Would be interested if anyone can suggest why the formula didn't work, but don't worry yourselves too much.

Thanks to Bryan for his input.

SamuelT

I replaced your .xls!data with a range and the Mid works ok to produce the required, providing that the 5 characters exist in the table.

I presume that you tested the Mid to see that the expected 5 characters were being selected, in fact A5 needs to be only 3 characters provided the single character exists in the table, but 5 would be the norm for the statement as written.

Does the table (where the match is expected) show a leading ' (tick) in the formula bar?

Otherwise, no idea.

---