How to make Xlookup return a status

[FaezMH]

Hi Guys,

How are you doing..

I have problem to make the Xlookup return a status such as "OK" if it meet the condition otherwise "BAD". At the moment I can only make it return the output in the Status column instead OK/BAD. I tried to use IF functions but nothing comes correctly.

Appreciate if you guys can help.

TEST SCAN METHOD 2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	OrderNo	BRAND	PartNo	Scanned Barcode	Status							BRAND	PartNo	Refrence Barcode
2	256-K	PRO	4AE6G1	3265189	3265189							PRO	4AA400AH	3265848
3	257-K	PRO	5AE6G1	3265653	3265653							PRO	4AA400BH	3265942
4	243-K	PRO	5EE6GB	3265548	3265548							PRO	4AE6G1	3265189
5	407-K	WILSON	4AD300A	76-581050-0ATECB	76-581050-0ATECB							PRO	5AE6G1	3265653
6	251-K	PRO	3AS65AA	3256102	3256102							PRO	5EE6GB	3265548
7	295-K	PRO	6EA6500H	3266102	3266102							PRO	7EA7GB	3265559
8	277-K	PRO	7EA7GB	3265559	3265559							PRO	3AS65AA	3256102
9	403-K	WILSON	43S520A	76-588660-1ATECK	76-588660-1ATECK							PRO	6EA6500H	3266102
10	268-K	PRO	4AA400AH	3265848	3265848							WILSON	4AD300A	76-581050-0ATECB
11	279-K	PRO	4AA400BH	3265942	3265942							WILSON	43S520A	76-588660-1ATECK
12	400-K	WILSON	5AP615B	76-588260-1ATGF	76-588260-1ATGF							WILSON	5AP615B	76-588260-1ATGF
13	420-K	WILSON	6AP615B	76-588760-1ATGK	76-588760-1ATGK							WILSON	6AP615B	76-588760-1ATGK
14
15
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=XLOOKUP(C2&D2,M$2:M15&$N$2:N15,$N$2:N15)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E13	Cell Value	="PLS CHECK"	text	NO

Thank you.

Faez

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	OrderNo	BRAND	PartNo	Scanned Barcode	Status							BRAND	PartNo	Refrence Barcode
2	256-K	PRO	4AE6G1	3265189	OK							PRO	4AA400AH	3265848
3	257-K	PRO	5AE6G1	3265654	BAD							PRO	4AA400BH	3265942
4	243-K	PRO	5EE6GB	3265548	OK							PRO	4AE6G1	3265189
5	407-K	WILSON	4AD300A	76-581050-0ATECB	OK							PRO	5AE6G1	3265653
6	251-K	PRO	3AS65AA	3256102	OK							PRO	5EE6GB	3265548
7	295-K	PRO	6EA6500H	3266102	OK							PRO	7EA7GB	3265559
8	277-K	PRO	7EA7Gc	3265559	BAD							PRO	3AS65AA	3256102
9	403-K	WILSON	43S520A	76-588660-1ATECK	OK							PRO	6EA6500H	3266102
10	268-K	PRO	4AA400AH	3265848	OK							WILSON	4AD300A	76-581050-0ATECB
11	279-K	PRO	4AA400BH	3265942	OK							WILSON	43S520A	76-588660-1ATECK
12	400-K	WILSON	5AP615B	76-588260-1ATGF	OK							WILSON	5AP615B	76-588260-1ATGF
13	420-K	WILSON	6AP615B	76-588760-1ATGK	OK							WILSON	6AP615B	76-588760-1ATGK
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=IF(XLOOKUP(C2&D2,M$2:$M$15&$N$2:$N$15,$N$2:$N$15,"BAD",0,1)=D2,"OK","BAD")

 

[FaezMH]

Thanks Kerryx....that's great.
Owh....I really never thought that I can actually put a = sign there. I have been trying since yesterday and wondering what went wrong.

Thanks again and really appreciate that.

Faez

 

[Kerryx]

Someone may come with something cleaner