formula to search a list for a keyword in another cell, then get a result from another cell

[brianaclark1967]

Afternoon all,

Hoping you can help with this one

I want to search a list for a set word. If found I want to pull the value from a specific cell

I've attached a sample sheet as an example

Thanks all

Brian

 

[bosco_yip]

Maybe,

In C2, enter formula :

=COUNTIF(E2:E29,B2&"*")

Regards
Bosco

 

[brianaclark1967]

Hi Bosco

Thanks for getting back to me.

I really wanted the result to be in cell G2

If the word C/lock from cell B2 appears in the list E2:E29, I wanted cell G2 to copy the number from cell C2

If the word doesn't appear in the list, return zero instead in cell G2

Regards

Brian

 

[bosco_yip]

Hi Bosco

Thanks for getting back to me.

I really wanted the result to be in cell G2

If the word C/lock from cell B2 appears in the list E2:E29, I wanted cell G2 to copy the number from cell C2

If the word doesn't appear in the list, return zero instead in cell G2

Regards

Brian

Then,

In G2 :

=IF(COUNTIF(E2:E29,B2&"*")>0,C2,0)

Regards
Bosco

 

[brianaclark1967]

Then,

In G2 :

=IF(COUNTIF(E2:E29,B2&"*")>0,C2,0)

Regards
Bosco

That's excellent Bosco,

Worked a treat, thank you so much.

Regards,

Brian