Look up and Return Specified Value

[kumara_faith]

Hi,

I have the following table:

Column A	Column B	Column C	Column D
Invoice	Status	Result	List
ABC123	No Match	No Match	GHT789
DEF456	No Match	No Match	HGR778
GHT789	No Match	Match	BHG778

In column C, the formula should check the value in Column A against the value in Column D and if it is a match, to return as "Match". Otherwise, to return the value in column B. I tried using xlookup but I cant get it right. The correct result is in column C. Appreciate all the help

 

[bebo021999]

C2:
=if(COUNTIF($D$2:$D$4,A2),"Match",B2)

 

[kumara_faith]

Thank you bebo. That worked and appreciate your time and patience.?

 

[maabadi]

Try one of these 3 methods:

Book1
	A	B	C	D
1	Invoice	Status	Result	List
2	ABC123	ABC123	NO MATCH	GHT789
3	DEF456	DEF456	NO MATCH	HGR778
4	GHT789		MATCH	BHG778
5	JKL012	JKL012	NO MATCH	STZ001
6	MNO345		MATCH	VWW234
7	PQR678	PQR678	NO MATCH	MNO345
8	STU901	STU901	NO MATCH	PPP578
9	VWX234	VWX234	NO MATCH	JDR012
10	YZA567		MATCH	YZA567
11
12	Invoice	Status	Result	List
13	ABC123	ABC123		GHT789
14	DEF456	DEF456		HGR778
15	GHT789		MATCH	BHG778
16	JKL012	JKL012		STZ001
17	MNO345		MATCH	VWW234
18	PQR678	PQR678		MNO345
19	STU901	STU901		PPP578
20	VWX234	VWX234		JDR012
21	YZA567		MATCH	YZA567
22
23	Invoice	Status	Result	List
24	ABC123	ABC123		GHT789
25	DEF456	DEF456		HGR778
26	GHT789		GHT789	BHG778
27	JKL012	JKL012		STZ001
28	MNO345		MNO345	VWW234
29	PQR678	PQR678		MNO345
30	STU901	STU901		PPP578
31	VWX234	VWX234		JDR012
32	YZA567		YZA567	YZA567
33
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=IF(C2="NO MATCH",A2,"")
C2:C10	C2	=IF(IFNA(INDEX($A$2:$A$10,MATCH(A2,$D$2:$D$10,0)),"")="","NO MATCH","MATCH")
B24:B32,B13:B21	B13	=IF(C13="",A13,"")
C13:C21	C13	=IF(IFNA(INDEX($A$2:$A$10,MATCH(A2,$D$2:$D$10,0)),"")="","","MATCH")
C24:C32	C24	=IF(IFNA(INDEX($A$2:$A$10,MATCH(A2,$D$2:$D$10,0)),"")="","",A2)

 

[kumara_faith]

Hi,

My apologies. I have an added criteria as follows:

Book2
	A	B	C	D	E	F
1	Invoice	Code	Status	Result	List	List 2
2	ABC123	WH	No Match	Match	GHT789	54
3	DEF456	10	No Match	No Match	HGR778	JH
4	GHT789	11	No Match	Match	BHG778	WH
Sheet1

In column D:
1) If the value in column A matches any of the values in column E, return as Match, otherwise return the value from column C
2) If the value in column B matches any of the values in column F, return as Match, otherwise return the value from column C
3) As long as either one of the above is a match with their respective list in column E or F, to return as Match, otherwise return the value from column C

Example of the correct result is in column C. Is there a way to modify the existing formula ? I tried as below but did not work. Appreciate all the help.

=IF(COUNTIF($F$2:$F$5,B3),"Match",IF(COUNTIF($E$2:$E$5,A3),"Match",C3)*C3)

 

[bebo021999]

Replace the last "*" by"," then it should works.
Or:
=IF(OR(COUNTIF($F$2:$F$5,B3),COUNTIF($E$2:$E$5,A3)),"Match",C3)

 

[kumara_faith]

Thank you bebo.

That worked. Appreciate your time and patience. Have a great day.