If Cell Contains three strings, return data in adjacent cell

Hi All,
I have been trying to get this right using SUMPRODUCT(ISNUMBER(SEARCH) but no luck. Any help is appreciated.

I the formula to return the Date in cell B2 of the 'Analysis sheet' for the row in Column B in the 'Search Data sheet' that has the strings 'Apple' and '1000000'.

in the attachement, the Date returned in Cell B2 of the analysis sheet should be 03/01/2019.

Please help. Thanks!

Put this in B2 of the analysis sheet:

=IFERROR(INDEX('Search Data'!C:C,MATCH("Apple_"&A2&"*",'Search Data'!B:B,0)),"")

Format as a date, then copy down as required.

Hope this helps.

Pete

This can be used in the example file that you have attached:

=SUMIFS('Search Data'!C:C,'Search Data'!B:B,"Apple_"&A2&"*",'Search Data'!B:B,"<>*XX*")

i.e. if there is only one record which matches the criteria - if you have multiple records they will be added together, so it will not be suitable.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

It doesn't matter if you have multiple records with XX in them, as they will be ignored.

Also, please do not quote the previous message - it is just clutter. Use the Reply button rather than Reply With Quote.

Pete