How do i nested IF AND OR with unique values and unique message

[AndyJR]

Hi,

Cell E3 display dynamic reference values between 000 to 333 ( e.i 000,001,002,003, etc,etc)
Each reference have their "own" measure values that is displayed on cells
F3,G3,I3,R3,T3
for example :

=IF(AND(OR(E3={"000"}),OR(F3={1,2,3,4}),OR(G3={4,6,10,14}),OR(I3={1,2,4,5}),OR(R3={5,6,7,8}),OR(T3={0,3,5,7})),"IN","OUT")

The formula work fine, at the cell Z3 it display correctly "OUT" (without quote)

but when i nested the exact same formula with Different reference and different values with "&" it display "OUT" "OUT" (twice)

please can someone tell me where is the error? or how i do nest both formula?

PS
in reality there are 24 formulas with different reference and values that i have to nested


Thank you !!

 

[AndyJR]

Hi Matty,

Make sense but i'm not sure how to start, i can start from scratch and watching your tube video..

But yes, it make sense.

 

[Matty]

Hi,

For example...

Main table below (E2:X12):

val	HTt	Sk	-	L3*3	-	-	-	-	-	-	-	-	9*L	-	Tran	-	-	REF	TEXT
012	1	5	-	6	-	-	-	-	-	-	-	-	0	-	0	-	-	12	OUT
001	1	2	-	1	-	-	-	-	-	-	-	-	4	-	19	-	-	1	IN
010	2	1	-	7	-	-	-	-	-	-	-	-	8	-	21	-	-	10	OUT
200	1	2	-	8	-	-	-	-	-	-	-	-	5	-	4	-	-	200	OUT
201	1	6	-	9	-	-	-	-	-	-	-	-	4	-	2	-	-	201	OUT
101	1	0	-	2	-	-	-	-	-	-	-	-	2	-	6	-	-	101	IN
020	1	1	-	0	-	-	-	-	-	-	-	-	3	-	5	-	-	20	OUT
000	2	4	-	4	-	-	-	-	-	-	-	-	8	-	7	-	-	0	IN
010	3	1	-	3	-	-	-	-	-	-	-	-	1	-	21	-	-	10	IN
000	2	4	-	5	-	-	-	-	-	-	-	-	8	-	7	-	-	0	OUT

<tbody>
</tbody>

Check table (Z2:AE6):

val	HTt	Sk	L3*3	9*L	Tran
000	2	4	4	8	7
001	1	2	1	4	19
010	3	1	3	1	21
101	1	0	2	2	6

<tbody>
</tbody>

Array formula in X3 and copied down:

=IF(ISNUMBER(MATCH(E3&"|"&F3&"|"&G3&"|"&I3&"|"&R3&"|"&T3,Z$3:Z$6&"|"&AA$3:AA$6&"|"&AB$3:AB$6&"|"&AC$3:AC$6&"|"&AD$3:AD$6&"|"&AE$3:AE$6,0)),"IN","OUT")

Does this do what you need?

Cheers,

Matty

 

[AndyJR]

Hi Matty,

I Think is super useful the table + your formula, but i'm thinking:
Reference and values are dynamic and not same values at all time, and sometimes they will match, other times dont,
that's why i was looking for the IF/AND/OR to include 3, even 5 values for each reference

i.e

=IF(AND(OR(E3={012}),OR(F3={1,2,3,4}),OR(G3={4,6,10,14})'....etc, etc

on the example (screen shot) i've posted This time matched because i added the values for that purpose.
the idea was to add some others values inside the formula (not just 1 value per row) for each Reference but Ill try to adapt the table and your formula to it...

Thank you so much matty, appreciate a lot!!!!!!!!!!!!!!!