Using MID within a formula

[SamuelT]

Hi all,

I'm trying to create a VLOOKUP formula that incorporates the MID
worksheet function and am having a few problems. I'll try to explain
what's going on...

In one spreadsheet I have a list of projects that are identified by a
ID number called a TU Number. It looks like this: TU103250148. In
another I have a list of projects that are identified by an ID number
called a PIMS number. The PIMS number is basically the first five
numerical characters of the TU Number; it looks like this: 10325

I can use the MID function to extract the PIMS number from the TU
number: =MID(A2, 3, 5)

However, what I can't do is get the VLOOKUP function to run the MID
function before it looks at my list of TU numbers. I don't know if this
is even possible. Can anyone suggest how (and if) this can be done?

TIA,

SamuelT

 

[Guest]

Without using MID function you can extract. try this one:

=VLOOKUP("??"&E1&"*",A1:B2,2,0)
A1:B200 - list of projects
E1: lookup value (TU Number)

 

[Bob Phillips]

=INDEX(N1:N100,MATCH(PIMS,--MID(M1:M100,3,5),0))

where column M is the project ID, N is the column you want to extract.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[SamuelT]

Many thanks Muhammed and Bob - both options solve the problem