Search using multiple condition

[amit_gupta123]

Hii

I have a long list of daywise payment table. Col A contains ID and Col B contains the party name to which payment is made. Sample is attached for single day. Such table is made for 25 days in a month. In successive month I have to check that same payment is made to same party with same ID. The ID is fixed in nature while name varies so require partial text search for that. I am attaching sample file. I tried using sumproduct formula in Col J but i want result mentioned in Col K.Please guide

 

[Somendra Misra]

Hi Amit,

Try below formula in column K:

=IFERROR(INDEX($B$1:$B$6,MATCH(1,--ISNUMBER(SEARCH($H$1&"*"&I1&"*",$A$1:$A$6&$B$1:$B$6)),0)),"No Result")

Regards,

 

[amit_gupta123]

Hii somendra...Thks for revert but can IF ERROR be replaced as I am using earlier version of excel

 

[amit_gupta123]

I have extracted the part of the formula suggested by you

=INDEX($B$1:$B$6,MATCH(1,--ISNUMBER(SEARCH($H$1&"*"&I1&"*",$A$1:$A$6&$B$1:$B$6)),0))

It's working ...thanks

 

[Somendra Misra]

Try using :

=IF(ISERROR(INDEX($B$1:$B$6,MATCH(1,--ISNUMBER(SEARCH($H$1&"*"&I1&"*",$A$1:$A$6&$B$1:$B$6)),0))),"no result",INDEX($B$1:$B$6,MATCH(1,--ISNUMBER(SEARCH($H$1&"*"&I1&"*",$A$1:$A$6&$B$1:$B$6)),0)))


Regards,

 

[amit_gupta123]

thanks

 

[Somendra Misra]

You are Welcome.

Regards,