Hi txksa:
I would have prefered you build the solution part by part -- however, since you have provided a very specific and clear description, the single formula to do what you want is:
=IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))=INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:L)),IF(INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:J))-INDEX($G$2:$L$1558,MATCH($T$2,$G$2:$G$1558,0),COLUMNS($G:K))>10,FALSE,"SomeThingElse"))
Please note I used the MATCH function in what you called part1 rather than using the VLOOKUP function that you were contemplating to use.
Then since I am using cell O4 to record the value, for Conditional Formating of cell O4, I used ...
Formula Is ... =O4=FALSE ... then FORMAT|Patterns -- green color
see the following simulation where the formula has been applied ...
y030423h1.xls |
---|
|
---|
| G | H | I | J | K | L | M | N | O | P | Q | R | S | T |
---|
1 | | | | | | | | | | | | | | |
---|
2 | 1 | 1 | 1 | 1 | 1 | 1 | | | | | | | | 7 |
---|
3 | 2 | 1 | 1 | 1 | 1 | 1 | | | | | | | | |
---|
4 | 3 | 1 | 1 | 1 | 1 | 1 | | | FALSE | | | | | |
---|
5 | 4 | 1 | 1 | 1 | 1 | 1 | | | | | | | | |
---|
6 | 7 | 1 | 1 | 14 | 3 | 14 | | | | | | | | |
---|
7 | 6 | 1 | 1 | 1 | 1 | 1 | | | | | | | | |
---|
8 | 5 | 1 | 1 | 14 | 3 | 14 | | | | | | | | |
---|
9 | 8 | 1 | 1 | 1 | 1 | 1 | | | | | | | | |
---|
10 | 9 | 1 | 1 | 1 | 1 | 1 | | | | | | | | |
---|
|
---|
Is this what you are looking for?
Please post back if it works for you now -- otherwise explain a little further and let us take it from there.