If the value is not found in specific range, then try to find it in another range

[zinah]

Hi,

I need a formula that can meet below statement:
If the value is not found in specific range, then try to find it in another range, if not found in both ranges, then NA.
Below is a sample data for more illustration:
If the ID is found in Range 1, then Range 1, if not found, then try to find in Range 2, if it's found, then Range 2, if not found in both ranges, then NA

Test File_LookupValue.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Expected Results						RANGE 1		RANGE 2
2	034	RANGE 1						ID		ID
3	035	RANGE 1
4	036	RANGE 1						003		040
5	037	RANGE 1						005		041
6	030	RANGE 1						006		042
7	031	RANGE 1						007		043
8	037	RANGE 1						008		044
9	071	RANGE 2						009		045
10	072	RANGE 2						010		046
11	073	RANGE 2						011		047
12	074	RANGE 2						012		048
13	035	RANGE 1						030		049
14	036	RANGE 1						031		068
15	068	RANGE 2						034		069
16	076	RANGE 1						035		070
17	077	RANGE 1						036		071
18	071	RANGE 2						037		072
19	097	NA						076		073
20	098	NA						077		074
Sheet1

thank you!

 

[Fluff]

How about

=IF(ISNUMBER(XMATCH(A2,$H$4:$H$20,0)),$H$1,IF(ISNUMBER(MATCH(A2,$J$4:$J$20,0)),$J$1,"NA"))

 

[zinah]

How about

=IF(ISNUMBER(XMATCH(A2,$H$4:$H$20,0)),$H$1,IF(ISNUMBER(MATCH(A2,$J$4:$J$20,0)),$J$1,"NA"))

Thank you so much, that worked perfectly great, really appreciate your usual innovative solutions!

 

[Fluff]

You're welcome & thanks for the feedback.