If row values found in another range, return a specific value, else leave original value intact

[TropicalMagic]

Hi all,



I would like to:



Check if row value in Column AN can be found in another workbook's Range("D2:D30")



If true, then return "APAC" in Column AM.

If false, do nothing and leave row value as it is.



However, my formula entered below is returning 1s and FALSE:


Range("AM2")

=IF(COUNTIF('[LOCATION & DISTRICT.xlsx]LOCATION'!$D$2:$D$30, AN2), AM2 = "APAC")



How to ensure that the row values returned are either "APAC" or original value?



Many thanks!

 

[bebo021999]

Try:
=IF(COUNTIF('[LOCATION & DISTRICT.xlsx]LOCATION'!$D$2:$D$30, AN2), "APAC",AM2 )

 

[TropicalMagic]

Try:
=IF(COUNTIF('[LOCATION & DISTRICT.xlsx]LOCATION'!$D$2:$D$30, AN2), "APAC",AM2 )

Thanks for responding!

However, I think I would have to create another column to store the formula to avoid circular referencing

 

[Fluff]

How about

=IF(COUNTIF('[LOCATION & DISTRICT.xlsx]LOCATION'!$D$2:$D$30, AN2), "APAC","")

 

[TropicalMagic]

How about

=IF(COUNTIF('[LOCATION & DISTRICT.xlsx]LOCATION'!$D$2:$D$30, AN2), "APAC","")

Hi, thanks for the response! However, the same issue persists, only "APAC" or blank rows remain due to circular referencing. Nonetheless, I have created another column to place the formula in

 

[Fluff]

If you are putting that formula into AM2 then there is no circular reference in that formula.

 

[TropicalMagic]

If you are putting that formula into AM2 then there is no circular reference in that formula.

Hi, I've instead 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.