Formula for Multiple Search....!

[Jeet]

Hi everyone...!

Can anyone help me on this pls..


1) I have a database of 100 double digit samples in a single column, each in separate cells (A1:A100)
2) I have 2 set of inputs.
3) Input 1 with 10 set of values & Input 2 also with 10 set of values.
Example:
Input 1 : [10,98,87,65,48,29,66,31,47,24] each in separate cells (A110:J110)
Input 2 : [63,28,11,58,05,39,81,73,23,40] each in separate cells (A111:J111)

4) What i require the formula to do is :

Step 1:It should take all Input 1 values as reference and look if any of its values is matching with the database (A1:A100). if any match found then proceed with next step.

Step 2: If match is found then take all Input 2 values as reference and look if any of the values is matching with the corresponding next row of database. if its matched then copy the values from database & display it in A114 or Highlight the cells

In short, it has to search a pair of cells in which the top cell ( Example A20 )should have a value matching any 1 value from Input 1 & the bottom cell ( Example A21 )should have a value match from Input 2.

If no match found then display NO MATCH FOUND.

Thanks in advance.

 

[Haseeb A]

Hello Jeet,

I hope I am following you correctly. try this formula.

=IFERROR(INDEX(A111:J111,LOOKUP(11,MATCH(N(OFFSET(A1,MATCH(A110:J110,A1:A100,0),)),A111:J111,0))),"No Match")

 

[Jeet]

Hi Haseeb,

Thanks for a your reply

The formula which you have given works, but not completely there are few drawbacks i would like to mention kindly help me to correct it.

1) Its displaying the output match value of input 2 alone, (please check the attached sheet for your reference, in cell A87 & A88 there is a match hence the output is showing 73 alone, but i want the output to show 10 & 73, i want the pair of matched cells to be shown in output).

2) The formula is not working when there are multiple matches, its just showing 1 output whereas i need all possible outputs.

Many Many thanks for the help..!

 

[Haseeb A]

Jeet,

Try this formula for the pair:

=IFERROR(INDEX(A110:J110,MATCH(11,INDEX(MATCH(N(OFFSET(A1,MATCH(A110:J110,A1:A100,0),)),A111:J111,0),)))&" & "&INDEX(A111:J111,LOOKUP(11,MATCH(N(OFFSET(A1,MATCH(A110:J110,A1:A100,0),)),A111:J111,0))),"No Match")

But the order in input could be any where. eg: A110 match in H111. If you want to check only above & below order of input's, eg: A110 & A111. B110 & B111 etc... will require some modification.

For the multiple results in a single cell, VBA is better & possible if you have more. So I have zero knowledge in VBA. Forum has bunch of VBA experts, hope some one could help you.

 

[Jeet]

Hi Haseeb

Thanks a lot for the effort you have put in helping me

i need the formula to check above & below order of input's only with multiple results, So i take your suggestion of finding a solution with VBA.

Even i have zero knowledge in VBA so i will post this in a new thread VBA Macro section, hope i ll get the solution there.

Regards
Jeet

 

[Jeet]

Hi everyone...!

Can anyone help me on this pls..

1) I have a database of 100 double digit samples in a single column, each in separate cells (A1:A100)
2) I have 2 set of inputs.
3) Input 1 with 10 set of values & Input 2 also with 10 set of values.
Example:
Input 1 : [10,98,87,65,48,29,66,31,47,24] each in separate cells (A110:J110)
Input 2 : [63,28,11,58,05,39,81,73,23,40] each in separate cells (A111:J111)

4) What i require the formula to do is :

Step 1:It should take all Input 1 values as reference and look if any of its values is matching with the database(A1:A100). if any match found then proceed with next step.

Step 2: If match is found then take all Input 2 values as reference and look if any of the values is matching with the corresponding next row of database. if its matched then copy the values from database & display it in A114 or Highlight the cells

In short, it has to search a pair of cells in which the top cell ( Example A20 )should have a value matching any 1 value from Input 1 & the bottom cell ( Example A21 )should have a value match from Input 2.

If no match found then display NO MATCH FOUND.

It should work also in the case of multiple match found.
If displaying of the output is not possible then the matched cells should be highlighted.

Please check the attached excel sheet, for your reference.
Thanks in advance.
Jeet

 

[Somendra Misra]

Hi Jeet...

I think you posted this question twice. I was bit confused with the output of your requirement. The same is being pointed in the attached file. If you can clear the same, hope I could help you on this issue.

Regards!

 

[Jeet]

Mr.somendar misra

yes i have posted this question in Excel formula's thread before posting here.

Now i have posted this question here because i was informed that for my problem the solution could be found in VBA coding only, since my required output should work with multiple possibilities.

I WILL TRY TO EXPLAIN MY REQUIREMENT IN SHORT :

Input 1 : [10,98,87,65,48,29,66,31,47,24] each in separate cells (A110:J110)
Input 2 : [63,28,11,58,05,39,81,73,23,40] each in separate cells (A111:J111)

Step 1: From the input 1 first take 10 then search if any cell from A1:A100 is having the value 10.

Step 2: If 10 is found then proceed to Step 3. if there is no 10 found then take the second value 98 form input 1 and repeat the Step 1.

Step 3: Now Check the cell below to the cell having the matched value 10, and see if it's having any value from input 2.

Example:

Let say, Cell A24 is found to be having the value 10, then check the value of the cell below it i.e) A25 .

If any of the Input 2 value [63,28,11,58,05,39,81,73,23,40] is found in A25 then either Both the cell A24 & A25 should be Highlighted or the pair A24 & A25 cell value should be copied into the output cell.

If the value of A25 doesn't match with any value from Input 2 values, then Proceed with Step 1.

Step 4: If None of the Input 1 values is found to be followed by Input 2 values then display NO MATCH FOUND in the output cell.

The possible matching of Input 1 & 2 values are given as below:

[ (10,63) (10,28) (10,11) (10,58) (10,05) (10,39) (10,81) (10,73) (10,23) (10,40) ]
[ (98,63) (98,28) (98,11) (98,58) (98,05) (98,39) (98,81) (98,73) (98,23) (98,40) ]
[ (87,63) (87,28) (87,11) (87,58) (87,05) (87,39) (87,81) (87,73) (87,23) (87,40) ]
[ (65,63) (65,28) (65,11) (65,58) (65,05) (65,39) (65,81) (65,73) (65,23) (65,40) ]
[ (48,63) (48,28) (48,11) (48,58) (48,05) (48,39) (48,81) (48,73) (48,23) (48,40) ]
[ (29,63) (29,28) (29,11) (29,58) (29,05) (29,39) (29,81) (29,73) (29,23) (29,40) ]
[ (66,63) (66,28) (66,11) (66,58) (66,05) (66,39) (66,81) (66,73) (66,23) (66,40) ]
[ (37,63) (37,28) (37,11) (37,58) (37,05) (37,39) (37,81) (37,73) (37,23) (37,40) ]
[ (41,63) (41,28) (41,11) (41,58) (41,05) (41,39) (41,81) (41,73) (41,23) (41,40) ]
[ (24,63) (24,28) (24,11) (24,58) (24,05) (24,39) (24,81) (24,73) (24,23) (24,40) ]

The formula has to check all the combinations as stated above & the input 1 value should be in top cell followed by the Input 2 value in below cell.

I hope after reading this, it would give a clear idea of what my requirements are.
Please let me know if any of this explanation is not clear, i ll try to further explain it.

Many Thanks in advance
Regards,
Jeet

 

[Abhijeet R. Joshi]

Hi Jeet,

You should firstly create 2 arrays of the inputs, find input1.parameter1 in range(A1:A100) and found match then define the activecell.offset(1,0).value as variable and find it in the array2(aka input2) and if found matching then highlight the same...

try to build a code yourself using the above and feel free to comment if you face any problems..

 

[NARAYANK991]

Hi Jeet ,

Check this file.

Narayan

 

[NARAYANK991]

Hi Jeet ,

Here is another version.

Narayan

 

[Somendra Misra]

Hi Jeet,

Please see the attached file. I had use VBA to create helper cells highlighted with light blue colour, and then use INDEX and MATCH to get the values in Output cell.


@NARAYANK991 .... Hi, there
Both your and mine code are working with Input 1 values which are appearing once in data. But when they are getting repeated more than once than the code is not giving the corresponding output. Consider 48 appearing thrice in input 1 & corresponding 65, 66, 85 appearing in input 2.

Jeet: Can this be a case with your file? Or if there will not be any such cases than both the solution are OK. Just see and tell.

Any solution for repetition cases?

Regards!

 

[Debraj]

Hi Jeet,

You dont have to create separate post for same query.. just raise your hand if you need to move it to another section.. or report your moderation related queries via Report button..
Both post has been joined together

 

[Jeet]

Hi Jeet,

You dont have to create separate post for same query.. just raise your hand if you need to move it to another section.. or report your moderation related queries via Report button..
Both post has been joined together

Sorry, i didn't know about this.
Anyway will take note of this & will follow it in future.

Many Thanks & Regards
Jeet

 

[Jeet]

Hi,
@NARAYANK991 & @Somendra Misra

First of all Thanks a ton for your efforts

I have checked both the files uploaded as @Somendra Misra said its working for only once & not for multiple outputs .

Anyway, i want to restructure the question now so that it will be easy for you to formulating, i will be very happy if i get solution for this. i will do the rest of the work manually by creating the same in multiple column and using the formula.

New Restructured Question :

Please check the New attached Excel sheet

With the database of 100 double digit samples in a single column from A1:A100.
And,
Input : (10 ) in cell A115 & (73 ) in cell A116.

Step 1: From the input first take 10 then search if any cell from A1:A100 is having the value 10.

Step 2: Now Check the cell below to the cell if it's having the value 73. if yes then both the cell should be highlighted.
Example:

Let say, Cell A24 is found to be having the value 10, then check the value of the cell below it i.e) A25 .

If the value 73 is found in A25 then Both the cell A24 & A25 should be Highlighted.

No need to copy the value in output cell & also no need to show NO MATCH in case of no result found.

JUST THE CONDITION SATISFYING CELLS SHOULD BE HIGHLIGHTED, i will look at it and copy manually by scrolling down. As far as its Highlighted its no problem.

Also it should work in multiple outputs

Example:

If you check the New attached Excel sheet the database is found to be having the values as shown below:

A24 - 10
A25 - 73

A39 - 10
A40 - 73

A67 - 10
A68 - 73

A81 - 10
A82 - 73

All the above pair of cells are satisfying the condition, Hence all the 8 cells should be highlighted.

Many Thanks & Regard
Jeet

 

[kchiba]

Hi Jeet,

Here is a solution using two helper columns, you will see that Rows 87 and 88 are the only ones with numbers in each row in columns B and C

hope this helps

kanti

 

[Jeet]

Hi Jeet,

Here is a solution using two helper columns, you will see that Rows 87 and 88 are the only ones with numbers in each row in columns B and C

hope this helps

kanti

Mr.Kanti,

Thank you for your reply, kindly check the post above your post i have restructured the question as its creating lots of confusions & difficult in arriving to a solution for it.

Have a look at the new file attached for further reference and provide your guidance on it.

Many Thanks & Regards,
Jeet

 

[kchiba]

Hi Jeet,

look at attached, please note that formulas will change if the Input is more than a single range/row

kanti

 

[NARAYANK991]

Hi Jeet ,

Check this file.

Narayan

 

[Jeet]

Hi Jeet,

look at attached, please note that formulas will change if the Input is more than a single range/row

kanti

Mr.kanti

Thank you very much, your formula worked perfectly

Is it possible to Remove the TRUE/FALSE and make the cells Highlighted as @NARAYANK991 did in his attached sheet...???

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

Many Thanks & Regards,
Jeet

 

[Jeet]

Hi Jeet ,

Check this file.

Narayan

Mr.Narayan thanks a lot

The sheet which you have send is not working if i change the Input values.

Can you please correct it.

(or)

Please look at the file sent by Mr.kanti above, its working perfectly, kindly remove the TRUE/FALSE and make the cells to be highlighted like what you did in the sheet which u have sent.

Many Thanks & Regards,
Jeet

 

[NARAYANK991]

Hi Jeet ,

Can you upload a file where it does not work ? I do not have any idea of what changes to make in the input values so that it will not work.

Narayan

 

[Jeet]

Hi Jeet ,

Can you upload a file where it does not work ? I do not have any idea of what changes to make in the input values so that it will not work.

Narayan

In the above example the Input are 10 & 73 ,

Based on the file sent by you if these inputs are changed let's say if i key in a new input values in the cell A110 as 48 & in the cell A111 as 65 then it is supposed to look for 48 & 65 and Highlight it. but its not making any changes & just highlighting the results for 10 & 73 alone.

Please check the attached file.

Many Thanks & Regards,
Jeet

 

[NARAYANK991]

Hi Jeet ,

Before you make any observations on a solution which has been posted , please make an effort to go through the solution , and not just the output.

If you go through the file that I uploaded earlier , and see how it works , you will know what changes you need to make to make it work.

If you still have difficulties , let me know.

Narayan

 

[Jeet]

Mr.Narayan,

Sorry for troubling as i have already mentioned in my earlier post i have zero knowledge in VBA/macro.

i couldn't see any formula in the file which you have sent i don't even know where to look for the formula in VBA/macro

This is the reason i never posted this question in VBA/MACRO thread since i dunno anything in it, i was told that the soultion could be attained only through VBA/Macro then only i posted it there

Am sorry if i wasted many of yours precious time

please help me, am trying to learn it

Many Thanks & Regards,
Jeet