Search for existence of multiple cell string values in a range

[ruggc1]

Hi - thanks in advance for any help here. Heres what i'm trying to do. I have a spreadsheet of order information containing several columns. Example below:

Order # Region Salesperson Country Product Color
1234 North Mike USA Widget Blue
4362 South Bob Mexico Wonka Brown
8792 East Mike Canada Widget Black

I'm trying to set up an audit of sorts that will check the data above against an approved master matrix of Region, Salesperson, Country to highlight errors where an order was booked to the incorrect Region or Salesperson for example. My audit matrix would be:

Region Salesperson Country
North Mike USA
North Mike Canada
North Mike Greenland
South Bob Mexico
South Bob Brazil
South Bob Chile

I'm envisioning a column to the right of Color in the spreadsheet above called "Audit" with a formula that would check Region, Salesperson, Country for each order # against the audit matrix of all possible approved combinations for error. In the above example, i would expect an error to show for order #8792 where it was booked to the incorrect Region.

Any help on how to conduct this audit search is appreciated. Thanks!

 

[DanteAmor]

Hi and welcome to MrExcel

Try this

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
1	Order	Region	Salesperson	Country	Product	Color	Audit		Region	Salesperson	Country
2	1234	North	Mike	USA	Widget	Blue			North	Mike	USA
3	4362	South	Bob	Mexico	Wonka	Brown			North	Mike	Canada
4	8792	East	Mike	Canada	Widget	Black	Combination does not exist		North	Mike	Greenland
5									South	Bob	Mexico
6									South	Bob	Brazil
7									South	Bob	Chile
sh1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(COUNTIFS($I$2:$I$7,B2,$J$2:$J$7,C2,$K$2:$K$7,D2)=0,"Combination does not exist","")

 

[ruggc1]

Awesome - thanks! I figured it wasn't that difficult .... just couldn't remember the best way to do it. Works great.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.