Help with complex search formula

[TehTOECUTTER]

Hello!

Here is the logic behind the formula I am searching for.

I have a cell where I enter values A1, say 1 through 10.

I have a master list of numbers 1 through 10, just for reference in the formula. Say B1:B10.

Lets say I enter the number 7 in A1.

I want another cell to then search a column (G4:G12) and a row (G12:O12), determine if 7 is missing from the column and the row, AND if it is the only value missing from the column and row that is not in the master list of 1-10, then return a value of TRUE or FALSE.

So if I type 7 in A1 and 8 is missing from the row and column from which it searches it returns a value of FALSE. But if it is the only number missing then return a value of TRUE.

I understand how to use VLOOKUP or MATCH to see if 7 is missing from the column and row, but I do not know how to then determine if it is the only number missing, referencing the master list.

Thanks for any help!

 

[ScripterYoda]

Hello!

I'm checking for every value in master list to check if value is missing from column and row (results in C1:C10), then I'm checking if value is an entered value (results in D1:D10), finally I'm checking if any of previous value is true (results in E1:E10). The result is checking if every value in E1:E10 is true (result is in cell E11). All these operations are combined into a single formula in cell B11. Is it what You had in mind?

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2	1	TRUE	FALSE	TRUE
2	H4:H12	2	FALSE	TRUE	TRUE
3	G12:P12	3	TRUE	FALSE	TRUE
4		4	TRUE	FALSE	TRUE			1
5		5	TRUE	FALSE	TRUE			3
6		6	TRUE	FALSE	TRUE			4
7		7	TRUE	FALSE	TRUE			5
8		8	TRUE	FALSE	TRUE			6
9		9	TRUE	FALSE	TRUE			7
10		10	TRUE	FALSE	TRUE			8
11		TRUE			TRUE			9
12								10	9	8	7	6	5	4	3	1
Arkusz1

Cell Formulas
Range	Formula
A2	="H4:H12"
A3	="G12:P12"
C1	=NOT(ISERROR(MATCH(B1,INDIRECT($A$2),0)+MATCH(B1,INDIRECT($A$3),0)))
C2	=NOT(ISERROR(MATCH(B2,INDIRECT($A$2),0)+MATCH(B2,INDIRECT($A$3),0)))
C3	=NOT(ISERROR(MATCH(B3,INDIRECT($A$2),0)+MATCH(B3,INDIRECT($A$3),0)))
C4	=NOT(ISERROR(MATCH(B4,INDIRECT($A$2),0)+MATCH(B4,INDIRECT($A$3),0)))
C5	=NOT(ISERROR(MATCH(B5,INDIRECT($A$2),0)+MATCH(B5,INDIRECT($A$3),0)))
C6	=NOT(ISERROR(MATCH(B6,INDIRECT($A$2),0)+MATCH(B6,INDIRECT($A$3),0)))
C7	=NOT(ISERROR(MATCH(B7,INDIRECT($A$2),0)+MATCH(B7,INDIRECT($A$3),0)))
C8	=NOT(ISERROR(MATCH(B8,INDIRECT($A$2),0)+MATCH(B8,INDIRECT($A$3),0)))
C9	=NOT(ISERROR(MATCH(B9,INDIRECT($A$2),0)+MATCH(B9,INDIRECT($A$3),0)))
C10	=NOT(ISERROR(MATCH(B10,INDIRECT($A$2),0)+MATCH(B10,INDIRECT($A$3),0)))
D1	=B1=$A$1
D2	=B2=$A$1
D3	=B3=$A$1
D4	=B4=$A$1
D5	=B5=$A$1
D6	=B6=$A$1
D7	=B7=$A$1
D8	=B8=$A$1
D9	=B9=$A$1
D10	=B10=$A$1
E1	=OR(C1,D1)
E2	=OR(C2,D2)
E3	=OR(C3,D3)
E4	=OR(C4,D4)
E5	=OR(C5,D5)
E6	=OR(C6,D6)
E7	=OR(C7,D7)
E8	=OR(C8,D8)
E9	=OR(C9,D9)
E10	=OR(C10,D10)
E11	=AND(E1:E10)
B11	{=AND(IF(NOT(ISERROR(MATCH(B1:B10,INDIRECT($A$2),0)+MATCH(B1:B10,INDIRECT($A$3),0))),TRUE(),B1:B10=$A$1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[TehTOECUTTER]

Hey thanks for your help I really appreciate it.

That is close to what I am looking for.

In your example, if you were to input the #2 in cell A1 and

Column H contained just numbers 1,3,4,5
Row 12 contained just numbers 6,7,8,9,10

I would need B11 to return a value of TRUE because the only number missing from the column AND the row combined is the number 2.

 

[ScripterYoda]

Ok, so we just need to change the fragment of formula calculated in column C. Now, we're trying to check if key value is in row AND column, so let's change it to see if key value is in row OR column.

Excel 2016 (Windows) 64 bit<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]P[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]H4:H12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]G12:P12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody></table>

Arkusz1​

<table cellpadding="2.5px" rules="all" style="width: 85%" width="85%"><tbody>[TR]
[TD]Worksheet Formulas<table cellpadding="2.5px" rules="all" style="width: 100%" width="100%"><thead><tr style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);">[TH="width: 10px"]Cell[/TH]
[TH]Formula[/TH]
</tr></thead><tbody><tr>[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD]="H4:H12"[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD]="G12:P12"[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B1,INDIRECT($A$2),0)),ISERROR(MATCH(B1,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD]=B1=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD]=OR(C1,D1)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B2,INDIRECT($A$2),0)),ISERROR(MATCH(B2,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD]=B2=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD]=OR(C2,D2)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B3,INDIRECT($A$2),0)),ISERROR(MATCH(B3,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD]=B3=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD]=OR(C3,D3)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B4,INDIRECT($A$2),0)),ISERROR(MATCH(B4,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD]=B4=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD]=OR(C4,D4)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B5,INDIRECT($A$2),0)),ISERROR(MATCH(B5,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD]=B5=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD]=OR(C5,D5)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B6,INDIRECT($A$2),0)),ISERROR(MATCH(B6,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D6[/TH]
[TD]=B6=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E6[/TH]
[TD]=OR(C6,D6)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C7[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B7,INDIRECT($A$2),0)),ISERROR(MATCH(B7,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD]=B7=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E7[/TH]
[TD]=OR(C7,D7)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C8[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B8,INDIRECT($A$2),0)),ISERROR(MATCH(B8,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD]=B8=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E8[/TH]
[TD]=OR(C8,D8)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B9,INDIRECT($A$2),0)),ISERROR(MATCH(B9,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD]=B9=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E9[/TH]
[TD]=OR(C9,D9)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD]=NOT(AND(ISERROR(MATCH(B10,INDIRECT($A$2),0)),ISERROR(MATCH(B10,INDIRECT($A$3),0))))[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]D10[/TH]
[TD]=B10=$A$1[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E10[/TH]
[TD]=OR(C10,D10)[/TD]
</tr><tr>[TH="width: 10px, bgcolor: #DAE7F5"]E11[/TH]
[TD]=AND(E1:E10)[/TD]
</tr></tbody></table>[/TD]
[/TR]
</tbody></table>
<table cellpadding="2.5px" rules="all" style="width: 85%" width="85%"><tbody>[TR]
[TD]Array Formulas<table cellpadding="2.5px" rules="all" style="width: 100%" width="100%"><thead><tr style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);">[TH="width: 10px"]Cell[/TH]
[TH]Formula[/TH]
</tr></thead><tbody><tr>[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD]{=AND(IF(NOT(AND(ISERROR(MATCH(B1:B10,INDIRECT($A$2),0)),ISERROR(MATCH(B1:B10,INDIRECT($A$3),0)))),TRUE(),B1:B10=$A$1))}[/TD]
</tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody></table>

 

[ScripterYoda]

Ok, my formatting doesn't seem to be working, so to sum up:

You need to change the formula in C1 cell from:
=NOT(ISERROR(MATCH(B1,INDIRECT($A$2),0)+MATCH(B1,INDIRECT($A$3),0)))
to:
=NOT(AND(ISERROR(MATCH(B1,INDIRECT($A$2),0)),ISERROR(MATCH(B1,INDIRECT($A$3),0))))
and respectively change other formulas in C column.

And the formula that encapsulates all this in one cell (B11) also changes accordingly from:
{=AND(IF(NOT(ISERROR(MATCH(B1:B10,INDIRECT($A$2),0)+MATCH(B1:B10,INDIRECT($A$3),0))),TRUE(),B1:B10=$A$1))}
to:
{=AND(IF(NOT(AND(ISERROR(MATCH(B1:B10,INDIRECT($A$2),0)),ISERROR(MATCH(B1:B10,INDIRECT($A$3),0)))),TRUE(),B1:B10=$A$1))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself