Criteria search

[ffgilby]

Hello,

I am looking for a way to enter criteria in cell A1 and find the match within that column (A), then also do the exact same, but in column B. Easy enough, but what I need is to search where criteria in column A aligns or is next to the criteria in column B. Each of these columns contain around 48,000 rows, and the criteria entered may be in each column upwards of 50 or more times, but there will only be 1 instance where criteria 1 and 2 will be adjacent, if they are at all. When they do align, column C will have either 0, 1, or 9
So what I am looking for is a way to do this search and match, possibly give the exact row that contains this match, or say yes or no, and then the text from column C
A co-worker and myself have been trying to come with a solution, but to nothing yet.
Any ideas would be greatly appreciated.

 

[taurean]

Welcome to the board.

If you can post sample data (search for html maker on the forum) then you will stand a better chance. This is guess based on your description (And I hope I've understood it correctly).

Excel Workbook
	A	B	C
1	K	K	ROW.NO.12
2	A	B
3	B	C
4	C	D
5	D	E
6	E	F
7	F	G
8	G	H
9	H	I
10	I	J
11	J	K
12	K	K
Sheet1

 

[T. Valko]

Hello,

I am looking for a way to enter criteria in cell A1 and find the match within that column (A), then also do the exact same, but in column B. Easy enough, but what I need is to search where criteria in column A aligns or is next to the criteria in column B. Each of these columns contain around 48,000 rows, and the criteria entered may be in each column upwards of 50 or more times, but there will only be 1 instance where criteria 1 and 2 will be adjacent, if they are at all. When they do align, column C will have either 0, 1, or 9
So what I am looking for is a way to do this search and match, possibly give the exact row that contains this match, or say yes or no, and then the text from column C
A co-worker and myself have been trying to come with a solution, but to nothing yet.
Any ideas would be greatly appreciated.

What version of Excel are you using?

 

[ffgilby]

Here is a sample of what I am dealing with. Like I said in the first post, there are nearly 48,000 rows to search. So, step 1: enter 5 digit code in A1 to search A2:A48000 to find match(s) if they exist; step 2: if match(s) found in column A, enter second 5 digit code in B1 to search B2:B48000 and find if criteria 1 and 2 exist next to each other in the same row, and if they do, then list the code in column C (0, 1,or 9).
Hope this is the information you are looking for.

Also to T. Valko, I will be using Excel 2003.

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 67px"> <col style="WIDTH: 68px"> <col style="WIDTH: 341px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td>[Search 1]</td> <td>[Search 2]</td> <td>[returns (n/a) or (field number below) if match found]</td></tr> <tr style="HEIGHT: 70px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td>
</td> <td>
</td> <td>Modifier
0=not allowed
1=allowed
9=not applicable</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right">10021</td> <td>C8950</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: right">10021</td> <td>C8952</td> <td style="TEXT-ALIGN: right">9
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5
</td> <td style="TEXT-ALIGN: right">10021</td> <td>G0345</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6
</td> <td style="TEXT-ALIGN: right">10021</td> <td>G0347</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7
</td> <td style="TEXT-ALIGN: right">10021</td> <td>G0351</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8
</td> <td style="TEXT-ALIGN: right">10021</td> <td>G0353</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9
</td> <td style="TEXT-ALIGN: right">10021</td> <td>G0354</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: right">10021</td> <td>J2001</td> <td style="TEXT-ALIGN: right">9
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: right">10021</td> <td>0213T</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: right">10021</td> <td>0216T</td> <td style="TEXT-ALIGN: right">9
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">19290</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">36000</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">36410</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">37202</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">62318</td> <td style="TEXT-ALIGN: right">9
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">62319</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19
</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64415</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64416</td> <td style="TEXT-ALIGN: right">0
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64417</td> <td style="TEXT-ALIGN: right">9
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64450</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64470</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64475</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64490</td> <td style="TEXT-ALIGN: right">1</td></tr></tbody></table>

 

[T. Valko]

Here is a sample of what I am dealing with. Like I said in the first post, there are nearly 48,000 rows to search. So, step 1: enter 5 digit code in A1 to search A2:A48000 to find match(s) if they exist; step 2: if match(s) found in column A, enter second 5 digit code in B1 to search B2:B48000 and find if criteria 1 and 2 exist next to each other in the same row, and if they do, then list the code in column C (0, 1,or 9).
Hope this is the information you are looking for.

Also to T. Valko, I will be using Excel 2003.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 67px"><COL style="WIDTH: 68px"><COL style="WIDTH: 341px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>[Search 1]</TD><TD>[Search 2]</TD><TD>[returns (n/a) or (field number below) if match found]</TD></TR><TR style="HEIGHT: 70px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>

</TD><TD>

</TD><TD>Modifier
0=not allowed
1=allowed
9=not applicable
</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>C8950</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>C8952</TD><TD style="TEXT-ALIGN: right">9

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>G0345</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>G0347</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>G0351</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>G0353</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>G0354</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>J2001</TD><TD style="TEXT-ALIGN: right">9

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>0213T</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">10021</TD><TD>0216T</TD><TD style="TEXT-ALIGN: right">9

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">19290</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">36000</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">36410</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">37202</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">62318</TD><TD style="TEXT-ALIGN: right">9

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">62319</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19

</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64415</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64416</TD><TD style="TEXT-ALIGN: right">0

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64417</TD><TD style="TEXT-ALIGN: right">9

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64450</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64470</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64475</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64490</TD><TD style="TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>

Try this array formula**:

=INDEX(C3:C25,MATCH(1,IF(A3:A25=A1,IF(B3:B25=B1,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[ffgilby]

Try this array formula**:

=INDEX(C3:C25,MATCH(1,IF(A3:A25=A1,IF(B3:B25=B1,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Biff, I tried copying that function exactly but it did not work, it just responded with #N/A every time. I know the index function was there, because I used the CTRL,SHIFT,ENTER when finishing the entry, which put the { } in.
What am I doing wrong?
Thanks.

 

[T. Valko]

Biff, I tried copying that function exactly but it did not work, it just responded with #N/A every time. I know the index function was there, because I used the CTRL,SHIFT,ENTER when finishing the entry, which put the { } in.
What am I doing wrong?
Thanks.

Here's a small sample file based on your posted data that demonstrates this.

ZZZffgilby.xls 16kb

http://cjoint.com/?AHqb4rJAWS9

 

[ffgilby]

I still cannot get this to work. Is there other functions or tables set up within the document? I have copied the function into my original document, no luck, tried copying the columns into the sample you made, no luck, tried modifying the function to include down to row 47994, but still nothing.
I know your function works, but does my document contain too many rows??
I am lost once again. Could you tell me step by step how you set up this sample?
If you would like to try your magic on the entire spreadsheet, I can e-mail it to you.
Once again, I appreciate the help.

 

[taurean]

Try this formula:
=SUMPRODUCT(--(A3:A25=A2),--(B3:B25=B2),C3:C25)
Caution: It will give correct result if the there's one match only as you have stated in your first post!

 

[T. Valko]

I still cannot get this to work. Is there other functions or tables set up within the document? I have copied the function into my original document, no luck, tried copying the columns into the sample you made, no luck, tried modifying the function to include down to row 47994, but still nothing.
I know your function works, but does my document contain too many rows??
I am lost once again. Could you tell me step by step how you set up this sample?
If you would like to try your magic on the entire spreadsheet, I can e-mail it to you.
Once again, I appreciate the help.

When you say it doesn't work what exactly do you mean? Do you get an error? If so, what does it say? Are you getting the #N/A error?

In your posted sample data there are mixes of both numeric data and text data. It may be that you have data type mismatches. You may have numbers that are formatted as text so that when you search for a number you can't find it because it's been formatted as text.

For example:

Numeric 12345 is not the same as text 12345.

This site describes some common data problems and how they affect formulas:

http://contextures.com/xlFunctions02.html#Trouble