Search & Conditional format - based on multiple criteria

[Slimline]

Hello,

Sheet 1 – Main table (has several fields inc. Product Name & Sale date)
Sheet 2 – criteria table (List of Product Name & corresponding Sale date )
Basically I am trying to search for my values in sheet 2 in the main sheet 1, which contains a lot of data.

I would like to know if there is formula I can put in conditional formatting that will highlight the rows in sheet 1 - if the row contains & matches both the 'product name' & 'sale date' that is in sheet 2.

Also the 'product name' in sheet 2 vary a little. So if the formula can look for an approx. match & then look up the date & if it also matches, I want that row in sheet 1 highlighted.

Sorry I am unable to put on a excel file at the moment for my query.
Any help would be greatly appreciated.
Thanks
Serena

 

[Faseeh]

Hi Slimline,

Please see attached file...Hope that helps.

 

[Slimline]

Hi Faseeh,

Thanks very much!

However, suppose in sheet 1 (cell a3) the product was listed as "prod 1" instead of "product 1" . Can you add any wildcards to find an approx match so that row A3 in sheet 2 still highlights?

 

[Somendra Misra]

Hi Slimline,

Can you post a list of how you actual product name will look like?

Regards,

 

[Faseeh]

Hi Slimline,

In that case you should be interested in only 1 that identifies your product. Is that correct?

 

[Slimline]

Hi Guys,

Suppose my product list contains one type of bottle ("Plastic Bottles") but it has been entered in a slightly different way (in the main sheet ) e.g:

Plastic red bottles 5ml
PlasticRX red bottles
PlasticRX R Bottles


I want the search (formula) to pick up "plastic" in the main sheet & if possible "bottles" aswell & then match/highlight it.

Can that be done by tweaking the current formula used by Faseeh in the uploaded file?

Regards
Serena

 

[Faseeh]

You can replace previous formula with this formula, press CSE to execute:

=SUM(IFERROR(SEARCH(Sheet1!$A$3:$A$6,Sheet2!$A3),FALSE))

..But a more organized way could be to introduce Product Codes in your data set and then look for them.