VLOOKUP with value contained within text
 

Can someone please help.

I am trying to perform a vlookup where the value is contained within the
text of the cell and there is no common pattern as to where the value starts
(eg could start in position 1 or 25)

An example:

The value is contained within the following cell in "Sheet 1" - A/C
M98603521 GOLDMAN SACH


Within the array in "Sheet 2"

Column A Column B
GOLDMAN SACH GOLSAC1012
....
....

I would like to lookup A/C M98603521 GOLDMAN SACH and return GOLSAC1012.

Thanks

Dan

VLOOKUP with value contained within text
 

Try one of these:

=LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),Sheet2!B$1:B$10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$1:A$10<""),,1 ),0))

If your table on sheet2 will not have any empty cells we can shorten each
formula a little:

=LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1))),,1),0))

Biff

"Farns" wrote in message
...
Can someone please help.

I am trying to perform a vlookup where the value is contained within the
text of the cell and there is no common pattern as to where the value
starts
(eg could start in position 1 or 25)

An example:

The value is contained within the following cell in "Sheet 1" - A/C
M98603521 GOLDMAN SACH


Within the array in "Sheet 2"

Column A Column B
GOLDMAN SACH GOLSAC1012
...
...

I would like to lookup A/C M98603521 GOLDMAN SACH and return
GOLSAC1012.

Thanks

Dan

VLOOKUP with value contained within text
 

The formula for the last match in the list can be shortened a little:

=LOOKUP(2,1/SEARCH(Sheet2!A$1:A$10,A2)/(Sheet2!A$1:A$10<""),Sheet2!B
$1:B$10)

or without blanks:

=LOOKUP(9^9,SEARCH(Sheet2!A$1:A$10,A2),Sheet2!B$1: B$10)

You could also add in the first line of sheet1 the return value when
the match is not found:

A B
* Not found
A/C M98603521 GOLDMAN SACH
.....

On 12 Mar, 02:30, "T. Valko" wrote:
Try one of these:

=LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),*Sheet2!B$1:B$10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))**(Sheet2!A$1:A$10<""),, 1),0))

If your table on sheet2 will not have any empty cells we can shorten each
formula a little:

=LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)*)),,1),0))

Biff

"Farns" wrote in message

...



Can someone please help.

I am trying to perform a vlookup where the value is contained within the
text of the cell and there is no common pattern as to where the value
starts
(eg could start in position 1 or 25)

An example:

The value is contained within the following cell in "Sheet 1" - A/C
M98603521 GOLDMAN SACH

Within the array in "Sheet 2"

Column A Column B
GOLDMAN SACH GOLSAC1012
...
...

I would like to lookup A/C M98603521 GOLDMAN SACH and return
GOLSAC1012.

Thanks

Dan- Hide quoted text -

- Show quoted text -

VLOOKUP with value contained within text
 

Thank you both very much.

Dan


"Lori" wrote:

The formula for the last match in the list can be shortened a little:

=LOOKUP(2,1/SEARCH(Sheet2!A$1:A$10,A2)/(Sheet2!A$1:A$10<""),Sheet2!B
$1:B$10)

or without blanks:

=LOOKUP(9^9,SEARCH(Sheet2!A$1:A$10,A2),Sheet2!B$1: B$10)

You could also add in the first line of sheet1 the return value when
the match is not found:

A B
* Not found
A/C M98603521 GOLDMAN SACH
.....

On 12 Mar, 02:30, "T. Valko" wrote:
Try one of these:

=LOOKUP(2,1/((ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)))*(Sheet2!A$ 1:A$10<"")),-Sheet2!B$1:B$10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX((ISNUMBER(SEA RCH(Sheet2!A$1:A$10,A1)))-*(Sheet2!A$1:A$10<""),,1),0))

If your table on sheet2 will not have any empty cells we can shorten each
formula a little:

=LOOKUP(2,1/ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)),Sheet2!B$1:B$ 10)

=INDEX(Sheet2!B$1:B$10,MATCH(1,INDEX(--(ISNUMBER(SEARCH(Sheet2!A$1:A$10,A1)-)),,1),0))

Biff

"Farns" wrote in message

...



Can someone please help.

I am trying to perform a vlookup where the value is contained within the
text of the cell and there is no common pattern as to where the value
starts
(eg could start in position 1 or 25)

An example:

The value is contained within the following cell in "Sheet 1" - A/C
M98603521 GOLDMAN SACH

Within the array in "Sheet 2"

Column A Column B
GOLDMAN SACH GOLSAC1012
...
...

I would like to lookup A/C M98603521 GOLDMAN SACH and return
GOLSAC1012.

Thanks

Dan- Hide quoted text -

- Show quoted text -