Vookup related query

[Manish Gurve]

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

 

[Jacob Skaria]

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]

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,1)

If this post helps click Yes

 

[Ashish Mathur]

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