VLOOKUP with value contained within text

[Guest]

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

 

[T. Valko]

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(SEARCH(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

 

[Lori]

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
.....

 

[Guest]

Thank you both very much.

Dan

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
.....