use function OR more than 5 logic numbers for 2 result

[Osama Fawzy]

Hi, i have a table consists of (5) column (A,B,C,D,E), the column E(TTSN) has more than 7 logic number from column A (Serial Number), and i want to chose from just 2 choice, but the formula IF(OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216") stop at the second number

Serial-Number	Date	TSN	CSN	TLB	TTSN
A9197	05-11-2021	16397	8299	000249	=IFERROR(IF(OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216"),VLOOKUP(H7,OSAMA\1-Aircraft''s Status\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)-F7/24,VLOOKUP(H7,OSAMA\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)),"")
A8538	12-06-2022	17722	8328	000250
43815	22-11-2021	20952	11609	000260
43637	15-11-2021	24201	13170	000270
43236	22-11-2021	23135	12400	000350
43059	16-11-2021	22959	12141	000370
14138	12-08-2020	23602	12214	000420

 

[etaf]

can you give an example of what you want to choose 2 from the more than 7

you could add the numbers into a table and then use count()

=IF( AND(Count(Table to lookup, value 1 ) >0, Count(Table to lookup, value 2 ) >0), vlookup1,vlookup2

But not sure i follow exactly what you are after

has more than 7 logic number from column A (Serial Number), and i want to chose from just 2 choice,

 

[Osama Fawzy]

can you give an example of what you want to choose 2 from the more than 7

you could add the numbers into a table and then use count()

=IF( AND(Count(Table to lookup, value 1 ) >0, Count(Table to lookup, value 2 ) >0), vlookup1,vlookup2

But not sure i follow exactly what you are after

thanks for u, the function "OR" has (9) logic numbers (A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216"), and tow choice VLOOKUP(H7,OSAMA\1-Aircraft''s Status\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)-F7/24,VLOOKUP(H7,OSAMA\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1), so the formula stop at logic number 2 (A7="8538"), i want the formula test all of (9) numbers and if one of them is true Implement first chose (VLOOKUP(H7,OSAMA\1-Aircraft''s Status\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)-F7/24) and if no one of (9) numbers implement the second chose (VLOOKUP(H7,OSAMA\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1), so the formula stop at logic number 2 (A7="8538")

 

[etaf]

=IF(OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216"),VLOOKUP(H7,OSAMA\1-Aircraft''s Status\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)-F7/24,VLOOKUP(H7,OSAMA\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1))
that should work OK
BUT - you have a mixture of Number and Text - that maybe be the issue

"14138" is text and so will not see the number 14138
so if the spreadsheet has those as numbers
then try
=OR(A7="A9197",A7="A8538",A7=43815,A7=43637,A7=43236,A7=43059,A7=14138,A7=17644,A7=14216)

now its looking for the number

=OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216")
will give a TRUE when it finds any of those as TEXT only
so using
IF( TEST , do if TRUE, do if No Match FALSE)

Book2
	A	B	C	D	E
1
2
3
4
5
6
7	A8538				TRUE
8	43637		a number		FALSE
9	43637		text		TRUE
10					FALSE
11
12
13	43637		number		TRUE
14	A8538		text		TRUE
15
Sheet1

Cell Formulas
Range	Formula
E7:E10	E7	=OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216")
E13:E14	E13	=OR(A7="A9197",A7="A8538",A7=43815,A7=43637,A7=43236,A7=43059,A7=14138,A7=17644,A7=14216)

 

[Osama Fawzy]

=IF(OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216"),VLOOKUP(H7,OSAMA\1-Aircraft''s Status\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1)-F7/24,VLOOKUP(H7,OSAMA\3-SU-NMG\[SU-NMG_2022.xlsb]AC TLB'!$A$7:$E$1199,5,1))
that should work OK
BUT - you have a mixture of Number and Text - that maybe be the issue

"14138" is text and so will not see the number 14138
so if the spreadsheet has those as numbers
then try
=OR(A7="A9197",A7="A8538",A7=43815,A7=43637,A7=43236,A7=43059,A7=14138,A7=17644,A7=14216)

now its looking for the number

=OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216")
will give a TRUE when it finds any of those as TEXT only
so using
IF( TEST , do if TRUE, do if No Match FALSE)

Book2
	A	B	C	D	E
1
2
3
4
5
6
7	A8538				TRUE
8	43637		a number		FALSE
9	43637		text		TRUE
10					FALSE
11
12
13	43637		number		TRUE
14	A8538		text		TRUE
15
Sheet1

Cell Formulas
Range	Formula
E7:E10	E7	=OR(A7="A9197",A7="A8538",A7="43815",A7="43637",A7="43236",A7="43059",A7="14138",A7="17644",A7="14216")
E13:E14	E13	=OR(A7="A9197",A7="A8538",A7=43815,A7=43637,A7=43236,A7=43059,A7=14138,A7=17644,A7=14216)

thanks a lot

 

[etaf]

was it the text / number issue ?