Formula for Multiple Search....!

[NARAYANK991]

Hi Jeet ,

First , can you specify the problem accurately ?

A formula based solution will be the easiest approach if your input is just one cell each.

A VBA based solution will be easier if your input is multiple cells.

First you mentioned that Input 1 is 10 cells , and Input 2 is 10 cells ; a VBA based solution was therefore the recommended one.

Now , you have reduced Input 1 to one cell , and Input 2 to one cell.

Which is the actual problem you want solved ?

Narayan

 

[Jeet]

Mr.narayan

Previously I wanted the solution for the Input 1 -10 cells & Input 2 - 10 cells because my database is huge, i just mentioned the database as 100 for example only in reality its over 12,473 cells. But, the thing is i dunno to communicate my problem properly it's completely my fault.

Anyway, if i get the solution for the Restructured Question which is Input 1 to one cell , and Input 2 to one cell.
it would be really grateful if you help me in getting solution for this.

One solution for this is already given by Mr.Kanti i need you to kindly do one modification in it, i want the result to be highlighted similarly as it was in the sheet given by you & not mentioned as TRUE/FALSE.

If possible please explain me the formula used & how it works..?
Also if i want to add more column then what changes i have to make in the formula.

Kindly check the attached solution file given by Mr.Kanti

Many Thanks & Regards,
Jeet

 

[NARAYANK991]

Hi Jeet ,

Can you check this file ?

Narayan

 

[Jeet]

Hi Jeet ,

Can you check this file ?

Narayan

Mr.Narayan

Millions & Millions of thanks..!!!

Am so happy, no words to describe it..! Its working perfectly as i was expecting. You made my work so easy thanks again

Can you please tell me what changes i should do in the formula:

1) If more columns to be added in the Database ..?
2) If More Input to be added ( At present its Input 1 & Input 2 only, what if Input 3 & Input 4 to be added )..?
3) If change the Input value cell location ( from A115 & A116 to other cell location ) ..?

Also Thanks a lot to everyone who shared their knowledge and put their effort for solving this post.

Many Thanks & Regards
Jeet

 

[NARAYANK991]

Hi Jeet ,

At present , the formula is simple ; for your points 1 and 3 , nothing much needs to be done ; I will explain the present setup , and you can easily modify it to take care of these two points.

However , if you need to take care of point 2 , things are not so easy ; VBA might be an easier option. Let us see. If you can upload a file with this data , we can see how best it can be done.

At present , there are 2 formulae which have been put in , in the CF feature.

1. =AND($A1=$A$115,$A2=$A$116)
2. =AND($A1=$A$115,$A2=$A$116)

You may wonder why the two formulae are identical ; the reason is that the range to which they apply are different ; the first formula applies to the range $A$2:$A$100 , while the second applies to the range $A$1:$A$99.

This entire setup is for one column , column A. If you now wish to extend this same principle to another column , say column D ( the two columns need not be adjacent ) , just select the ranges , first $D$1:$D$99 , and thereafter $D$2:$D$100 , and use the following two formulae :

1. =AND($D1=$A$115,$D2=$A$116)
2. =AND($D1=$A$115,$D2=$A$116)

That's all there is to it.

If you wish to change your input cells range from A115 and A116 to say J17 and J19 ( again the two cells need not be adjacent ) , change the above formulae to :

1. =AND($A1=$J$17,$A2=$J$19)
2. =AND($A1=$J$17,$A2=$J$19)

for your database in column A. For any additional columns , change the formulae accordingly.

For your point 2 , please upload a file with the appropriate data , and we can suggest a suitable solution.

Narayan

 

[Jeet]

Mr.Narayan

Thanks a lot for your explaination.
i will upload the original file shortly.

Many Thanks & Regards,
Jeet

 

[Jeet]

Mr.Narayan

Kindly check the attached Original file for your perusal.

Many Thanks & Regards,
Jeet