Lookups & match ????

[starfishgooner]

Hi Can anybody help?? I am trying to lookup a numeric value amonsgt text in
a colunm, match this numeric value exactly in another column and then return
a value from a third column to a new column. For example:

column 1, cell 1
'corr/joebloggs/445544/transfer'

I want to find the '445544' sitting in column 2, then return a value (say
12345) sitting in column 3 to a new column, 4

Its confusing me just writing this!!! Please help!!

 

[Jacob Skaria]

In cell A1:
corr/joebloggs/445544/transfer

If you mean 445544 in any of the row in column B and a corresponding number
in ColumnC in the same row

In cell D
=INDEX(C:C,MATCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),B:B,0))

If this post helps click Yes

 

[starfishgooner]

Hi Jacob,

If I can expand my example, it may help me and you!! I have:

Col A Col B Col C
Col D
'corr/joebloggs/445544/transfer' 263636 8701111 MATCH COL A&B to
give C
'corr/joebloggs/554455/transfer' 263637 8702222 MATCH COL A&B to
give C
'corr/joebloggs/263636/transfer' 554455 8703333 MATCH COL A&B to
give C
'corr/joebloggs/263637/transfer' 445544 8704444 MATCH COL A&B to
give C

So for the first row, the result in D1 should match A1 with B4 to give
number found in C4.

I do hope you can help.....Thanks very much,

Andy

 

[Glenn]

Assuming the desired number will always be followed by "/", try this array
formula (commit with CTRL+SHIFT+ENTER):

=VLOOKUP(--LEFT(SUBSTITUTE(MID(A1,MATCH(1,
--ISNUMBER(--MID(A1,ROW($1:$1000),1)),0),LEN(A1)),
"/",REPT(" ",99)),99),$B$1:$C$1000,2,FALSE)

Adjust the range near the end ($B$1:$C$1000) to match your data.

 

[Jacob Skaria]

Did you try the formula?

If this post helps click Yes

 

[Glenn]

In my newsreader, when I copy the formula from your original message and paste
in Excel, I get "#N/A". When I looked closer, it was because I ended up with
"9999" instead of what you intended.

When quoted below, the formula shows "99 ^ 99" (without the spaces) and copies
OK. But the original message showed the second 99 in superscript without the
"^" and caused the error.

Could be why it didn't work for the OP...

 

[Teethless mama]

Try this:

=VLOOKUP(TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),198,99))+0,$B$1:$C$4,2,0)

 

[Jacob Skaria]

Thanks Glenn..I was wondering what went wrong !!

If this post helps click Yes