Compare a table cell value to see if it matches a value from multiple values from another table

[gberg]

I have two tables:
- The first table lists Items and the values that are acceptable for the "UOM" (Unit of Measure)
- The second table lists Items and has associated quantities with their UOM

I want a formula to verify that the UOM used in the second table is one that is listed in the first table and returns "UOM Correct" if it is or "UOM Incorrect" if it is not

In this example the second table Item 1 is incorrect because "EA" is not one of the values for Item 1 from the fist table. Item 2 is correct because "BUND" is one of the values from the first table.

Item	UOM 1	UOM 2	UOM 3
Item 1	SH	SF	BUND
Item 2	EA	LF	BUND
ITEM	QTY	QTY UOM	QTY UOM ERROR
Item 1	100	EA	UOM Incorrect
Item 2	100	BUND	UOM Correct

Thanks for your help

 

[Peter_SSs]

Is this what you are after?

23 02 28.xlsm
	A	B	C	D
1	Item	UOM 1	UOM 2	UOM 3
2	Item 1	SH	SF	BUND
3	Item 2	EA	LF	BUND
4
5
6	ITEM	QTY	QTY UOM	QTY UOM ERROR
7	Item 1	100	EA	UOM Incorrect
8	Item 2	100	BUND	UOM Correct
gberg

Cell Formulas
Range	Formula
D7:D8	D7	="UOM "&IF(ISNUMBER(MATCH(C7,INDEX(B$2:D$3,MATCH(A7,A$2:A$3,0),0),0)),"Correct","Incorrect")

 

[gberg]

Peter,
You've helped me in the past and as always you make it look easy! That did exactly what I needed. Thank you for your help, again!

Greg

 

[Peter_SSs]

You're welcome once again.