Hello,
i am looking an Excel fomula :
In one Sheet i have exported the data from a tool with inside the colmun A information and client name
In the other sheet i have in column B the list of clients (clean list)
Please see the example :
Sheet1, column A :
End User: Coupland Cardiff contract 2245503
End User: Sanofi contract 14785258
End User: South Yorkshire contract 2257458
etc,
Sheet2, column B :
Coupland Cardiff
Sanofi
South Yorkshire
etc,
Today i am using this formula in order to extract the only the client name from Sheet1 column A if it's found in Sheet2 column B :
In B2 from Sheet1 =IFERROR(LOOKUP(PI(),1/COUNTIF(A2,"*"&'Sheet2B2:B4&"*"),'Sheet2B2:B4),"")
It's working but the formula is not working if i add a new client name in sheet2 column B.
I am trying to find something dymanique, like not take the list (Sheet2B2:B4) but all the column (sheet2B:B) in order to find in my sheet1 when i add also a new client name
Thanks a lot for your help
i am looking an Excel fomula :
In one Sheet i have exported the data from a tool with inside the colmun A information and client name
In the other sheet i have in column B the list of clients (clean list)
Please see the example :
Sheet1, column A :
End User: Coupland Cardiff contract 2245503
End User: Sanofi contract 14785258
End User: South Yorkshire contract 2257458
etc,
Sheet2, column B :
Coupland Cardiff
Sanofi
South Yorkshire
etc,
Today i am using this formula in order to extract the only the client name from Sheet1 column A if it's found in Sheet2 column B :
In B2 from Sheet1 =IFERROR(LOOKUP(PI(),1/COUNTIF(A2,"*"&'Sheet2B2:B4&"*"),'Sheet2B2:B4),"")
It's working but the formula is not working if i add a new client name in sheet2 column B.
I am trying to find something dymanique, like not take the list (Sheet2B2:B4) but all the column (sheet2B:B) in order to find in my sheet1 when i add also a new client name
Thanks a lot for your help