Vlookup for value in group of text

[Baldev Kumar]

Please help me in using the vlookup function.
Example sheet attached.

[Aladin Akyurek]

In B2 of Sheet1 in your file enter and copy down:

=VLOOKUP(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0,$G$2:$H$149,2,0)

[BadlySpelledBuoy]

Another way, in B2 and copy down:

Please Login or Register  to view this content.

It's not pretty, but it works.

BSB.

[Baldev Kumar]

Great, this is working

[Aladin Akyurek]

What did you pick out?

[Baldev Kumar]

Both are working fine for me
but if i add a digit anywhere before the lookup value then Aladin formula return to #Value error
and if add any thing after lookup value, both formula return with error.

[Aladin Akyurek]

If error trapping is not what you are after, that is:

Please Login or Register  to view this content.

try to provide examples of look up values that are also to be found in your look up table?

[Baldev Kumar]

Example sheet attached

[Aladin Akyurek]

Would you agree that no code matches the ones listed below?

TRF CHA-SCB :- 01871030000000107

and

CHA-SCB :- 02711030000000019

[Baldev Kumar]

each code working fine. But no one is working if

"TRF CHA-SCB :- 01871030000000107" will become "TRF CHA :- 01871030000000107 SCB"

[Aladin Akyurek]

I don't follow. Did you see my question in post #9?

[Baldev Kumar]

Please see the Example sheet in post #10,
I have change the data in cell A2, A3, A4 cells and the formula return to error massage

[samba_ravi]

see the attached file in Yellow Color

[Baldev Kumar]

Now this is this working with my every condition,
Thanks Siva, Aladin and BSB

[Aladin Akyurek]

So, the answer to my question would have been yes...

The formula then I had for you is:

=LOOKUP(9.99999999999999E+307,FIND($G$2:$G$13,$A2),$H$2:$H$13)

Or:

=IFERROR(LOOKUP(9.99999999999999E+307,FIND($G$2:$G$13,$A2),$H$2:$H$13),"")

which should be faster.