Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using MID within a formula
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 -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=574103 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using MID within a formula
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) "SamuelT" wrote: 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 -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=574103 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using MID within a formula
=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" wrote in message ... 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 -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=574103 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using MID within a formula
Many thanks Muhammed and Bob - both options solve the problem! -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501 View this thread: http://www.excelforum.com/showthread...hreadid=574103 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |