Multi Value Compare Function/Equation?

Hi all,
I have a data dump of values that I need to inspect to see if they're valid. I have a table of acceptable values in the same workbook that I want to use as a legend/answer key for the data dump.

The attached screenshot gives you an idea of what I'm trying to do. For each row on the left side, I need to compare the Value 1, Value 2, and Value 3 entries with the Acceptable values on the right side.

If all 3 values on the left line up with 1 row in the Acceptable Values table, the I write "OK" in the Results column. Otherwise I write "BAD!".

The trick here is that the data dump is thousands of lines long, and the Acceptable Values table is about 110 rows long.

Any ideas on how I can automate this lookup process by using a function or equation?
Thanks!

Originally Posted by martindwilson

please don't post images!

I believe it's clear that the image helps show the setup infinitely better than a page of text.

Originally Posted by martindwilson

nope it means someone has to replicate your work sheet to create a solution,my secretary just left

Any idea when she's back?

Should I attach the spreadsheet then too?

If the columns are split, this works

=IF(SUMPRODUCT((B2>=$F$2:$F$11)*(B2<=$G$2:$G$11)*($H$2:$H$11=C2)*($I$2:$I$11=D2))=1,"OK","BAD")


Cool huh?

Originally Posted by martindwilson

well done then !

I knew you'd be happy!