Searching a partial string within a column, and returning a value.

[edward8503]

Hello All,

I haven't used these forums before, I hope someone will be able to help!

Basically, I have two sheets:

In one sheet, I have a column of descriptions:

Column A:
Accrual for ADP
Grant true up
Consulting FESCO fee

In the second sheet, I have a column of keywords and associated vendors:

Column A, Column B
ADP, ADP INC
Grant, GRANT THORNTON LLP
E&Y, ERNST & YOUNG LLP
FESCO, FESCO CONSULTING

For each description, I wanted to see if I could find a way to search the description for a keyword, then return the vendor associated with that keyword.

For example: Since "Accrual for ADP" has the keyword "ADP" in it, it would return "ADP INC" in the cell.

I was trying index() and find() functions, but to no avail. I would appreciate any help!

 

[Matty]

Welcome to the Board!

Try like this:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:140px;"><col style="width:155px;"><col style="width:13px;"><col style="width:52px;"><col style="width:155px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Accrual for ADP</td><td>ADP INC</td><td>
</td><td>ADP</td><td>ADP INC</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Grant true up</td><td>GRANT THORNTON LLP</td><td>
</td><td>Grant</td><td>GRANT THORNTON LLP</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Consulting FESCO fee</td><td>FESCO CONSULTING</td><td>
</td><td>E&Y</td><td>ERNST & YOUNG LLP</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>
</td><td>
</td><td>
</td><td>FESCO</td><td>FESCO CONSULTING</td></tr></tbody></table>
Formula in B1 copied down is:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&D$1:D$4&" "," "&A1&" "),E$1:E$4)

Matty

 

[edward8503]

Works perfectly. Thanks!

 

[Matty]

Works perfectly. Thanks!

No worries. Happy to help.

Matty