Extracting data after matching data by combining

Dear Sir,

In the enclosed workbook,I want to extract the data in Column A3 and thereafter in Report sheet from data sheet of Column C by combinedly matching C3+D3+E3 of Report sheet with that of data of Column B of data sheet.Even though there are few more data in B column inaddition to & after combining C3+D3+E3 of Report sheet.One should have to ignore this portion as three column data combining is enough.

I will be eagerly waiting for the reply.

If you require any further information with regard to this,then kindly let me know.I know even though 100% can't be done but approx. would be suffice to me.

With Best Rgds

Suresh

Here is a solution. I put this Formula in G3 of Report as an array and dragged it down.

=INDEX(data!$C$2:$C$971,MATCH(B3&"111",data!$A$2:$A$971&(ISNUMBER(SEARCH(C3,data!$B$2:$B$971))+0)&(ISNUMBER(SEARCH(D3,data!$B$2:$B$971))+0)&(ISNUMBER(SEARCH(E3,data!$B$2:$B$971))+0),0))

Your data needs to be consistent. For example I removed 10 examples of extra spaces in Col B of Report (i.e. D 1459 instead of D1459). If the data is not consistent, then you won't get a match.
Another example, row 7, no match. You are searching for a match for TR Detergent for D1463. There is none. If something else means the same thing, then you need to do a search and replace and make them the same.
Anyway, this works for most of your data. See attached.

Dear Sir,

Thanks for the reply and helping me.

I did one mistake more to say.I forget to say that it should also match Column A of data sheet with Column of B of Report Sheet in addition to above said so far.Hence this would be multiple match criteria case by combining.

i.e 2nd Criteria would be that of Column B of Report sheet also must match with Column A of Data Sheet, in addition to above mentioned of 1st criteria of combined data of C3+D3+E3 of Report sheet with that of Column B of data sheet.

Kindly revise your formula by taking into consideration this one more criteria to get the exact result which is currently displayed in Column A of Report.

Hopefully,I could the desired result from you and from other forum contributors.As now this is a complete information which previously I was intially missing or lacking and was creating a problem to solve and understand to others.

With Best Rgds,
Suresh

Seems like there's more to it than that. Looking at your data, if not all criteria are met, then pick the best fit (i.e. meets 2 out of 3 criteria). and that could mean a match with col D,E or C,D or C,E. I'm not sure how to do that outside of having multiple nested IFERROR statements. Maybe someone else can pick it up.

Originally Posted by ChemistB

Here is a solution. I put this Formula in G3 of Report as an array and dragged it down.

=INDEX(data!$C$2:$C$971,MATCH(B3&"111",data!$A$2:$A$971&(ISNUMBER(SEARCH(C3,data!$B$2:$B$971))+0)&(ISNUMBER(SEARCH(D3,data!$B$2:$B$971))+0)&(ISNUMBER(SEARCH(E3,data!$B$2:$B$971))+0),0))

Your data needs to be consistent. For example I removed 10 examples of extra spaces in Col B of Report (i.e. D 1459 instead of D1459). If the data is not consistent, then you won't get a match.
Another example, row 7, no match. You are searching for a match for TR Detergent for D1463. There is none. If something else means the same thing, then you need to do a search and replace and make them the same.
Anyway, this works for most of your data. See attached.

Dear Sir,

Besides stating other information,can you trim each and every input data before matching in your single formula.Becoz,I think there might be in both sheet extra spaces.

In addition to it,you can use interchangebly * and x which might be creating an error to get the result.Also,you can take- TR only as prefix agst 'TR Detergent',TR LAMINATED,TR TUBE,TR ( L ),etc. and MILKY as prefix agst MILKY CC and so on of D column of Report sheet.From Column E and F of Report sheet you can take only numeric character further to minimise the remaining error which is currently displaying in the formula result which you have suggested.


Hope I could be able to make you understand and you can revise your existing formula to further minimise the error taking into consideration above things which I have said.

Thanks once again.

With Best Rgds,
Suresh