Check if row values in column cannot be found in another range

[TropicalMagic]

Hi all,


I would like to:


Check if row values in Column AP cannot be found in another workbook's Range("D2:D31"), return "NULL" in Column AO

Else, return row values in corresponding row of Column AN in Column AO


I'm not sure how to create a formula which would find row values which do not exist in a range...


Many thanks!

 

[Peter_SSs]

Is this what you mean?
I have done this in a single sheet but it can be adapted to look in another worksheet/workbook. Post back with more details if you need more help with the adaptation or if I have not interpreted correctly what you want.

Sample data and expected results with XL2BB usually help clarify the requirement(s).

22 03 04.xlsm
	D	E																																		AM	AN	AO	AP
1
2	a																																				k	NULL	x
3	b																																				m	m	d
4	c																																				j	NULL	q
5	d																																				h	NULL	w
6	e																																				n	n	b
7																																					b	b	d
8																																					x	x	e
9
Check other column

Cell Formulas
Range	Formula
AO2:AO8	AO2	=IF(ISNUMBER(MATCH(AP2,D$2:D$31,0)),AN2,"NULL")

 

[TropicalMagic]

Is this what you mean?
I have done this in a single sheet but it can be adapted to look in another worksheet/workbook. Post back with more details if you need more help with the adaptation or if I have not interpreted correctly what you want.

Sample data and expected results with XL2BB usually help clarify the requirement(s).

22 03 04.xlsm
	D	E																																		AM	AN	AO	AP
1
2	a																																				k	NULL	x
3	b																																				m	m	d
4	c																																				j	NULL	q
5	d																																				h	NULL	w
6	e																																				n	n	b
7																																					b	b	d
8																																					x	x	e
9
Check other column

Cell Formulas
Range	Formula
AO2:AO8	AO2	=IF(ISNUMBER(MATCH(AP2,D$2:D$31,0)),AN2,"NULL")

Hi, thanks for your response! Appreciate the sample workbook layout provided!


However, I decided to create more columns instead of confining the output to a single column,

ORIG_COUNTRY column row values may not be in the Range("D2:D31") despite having "APAC" ORIG_REGION column row value.

Hence, I included a new column, ORIG_COUNTRY_IN_LOCATION_LIST? =IF(COUNTIF(LOCATION'!$D$2:$D$31,AS2),"Y","N") to check if ORIG_COUNTRY column row values are in the Range("D2:D31").

Next, I created a new column, ORIG_COUNTRY_APAC REGION_BUT_NOT_IN_LIST =IF(AND(AN2="APAC",AO2="N"),"NULL","FALSE") to check if both the ORIG_REGION = "APAC" and ORIG_COUNTRY_IN_LOCATION_LIST? = "N"

Those row values with "NULL" means the ORIG_REGION row value = "APAC" but ORIG_COUNTRY row value is not of "APAC" ORIG_REGION (Wrong information)

Finally, I created a new column, FINAL_ORIG_REGION =IF(AP2="FALSE",AN2,"NULL") to output row values as "NULL" if corresponding row values ORIG_REGION = "APAC" and ORIG_COUNTRY_IN_LOCATION_LIST? = "N", correcting the wrong information


Many thanks and hope this helps!


You can refer to the screenshot's final 2 rows for more clarification.

 

[Peter_SSs]

So you are all sorted now?

 

[TropicalMagic]

So you are all sorted now?

Yes, thank you very much for your help!

 

[Peter_SSs]

No problem. Cheers.