Vookup related query
Hi
I have a column like this contained in cell a1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- another column is like this in cell d1 SBINH09222318684 can i put a vlookup between this two. in such a way that formula should find the second value as lookup_value in cell a1. Can a combination of mid & vlookup will solve this ? pls help. thanks in advance. As I have to make a bank reco and the data specified in a1 is in such a way that I can not seperate that no from the whole list. Thanks in advance |
Vookup related query
If I understand you correctly you are looking to get the data starting with
SBI. With the below data in cell A1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- the below formula will extract the number you are looking for... =TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH(":",A1)+1,99), " ",REPT(" ",99)),99)) If this post helps click Yes --------------- Jacob Skaria "Manish Gurve" wrote: Hi I have a column like this contained in cell a1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- another column is like this in cell d1 SBINH09222318684 can i put a vlookup between this two. in such a way that formula should find the second value as lookup_value in cell a1. Can a combination of mid & vlookup will solve this ? pls help. thanks in advance. As I have to make a bank reco and the data specified in a1 is in such a way that I can not seperate that no from the whole list. Thanks in advance |
Vookup related query
For a VLOOKUP try the below where Column D is with the numbers
=VLOOKUP(TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH(":",A1 )+1,99)," ",REPT(" ",99)),99)),D:D,1) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: If I understand you correctly you are looking to get the data starting with SBI. With the below data in cell A1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- the below formula will extract the number you are looking for... =TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH(":",A1)+1,99), " ",REPT(" ",99)),99)) If this post helps click Yes --------------- Jacob Skaria "Manish Gurve" wrote: Hi I have a column like this contained in cell a1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- another column is like this in cell d1 SBINH09222318684 can i put a vlookup between this two. in such a way that formula should find the second value as lookup_value in cell a1. Can a combination of mid & vlookup will solve this ? pls help. thanks in advance. As I have to make a bank reco and the data specified in a1 is in such a way that I can not seperate that no from the whole list. Thanks in advance |
Vookup related query
Hi,
Try this array formula (Ctrl+Shift+Enter). This assumes that there will always be a space after the account number I.e. there will always be a space before Mahak. Also, there will always be a : before the SBINH. Have assume that the data to be extracted in column =INDEX($A$1:$C$2,MATCH(TRUE,ISNUMBER(SEARCH(":"&D1 &" ",A1:A2)),0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Manish Gurve" <Manish wrote in message ... Hi I have a column like this contained in cell a1 BY TRANSFER- R:SBINH09222318684 MAHAK MARKETING-- another column is like this in cell d1 SBINH09222318684 can i put a vlookup between this two. in such a way that formula should find the second value as lookup_value in cell a1. Can a combination of mid & vlookup will solve this ? pls help. thanks in advance. As I have to make a bank reco and the data specified in a1 is in such a way that I can not seperate that no from the whole list. Thanks in advance |
All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com