Sheeloo,

Good morning. I really appreciate the time you're putting into solving my
problem, but its still not returning the results I was looking for - even
without trying to nest the formulae into a single cell. I'd like to send you
the file I'm working on but I don't see a way to attach it to the Discussion
Group e-mails. If you'll send an e-mail to my work account I'll send you the
file. My work account is .

Again, I really do appreciate your help & patience on this deal. Once we
get it all sorted out, I want to study the formulae you've created more
closely so I can better understand how these things work. Your formulae are
much more complex than anything I've ever created.

Russ


"Sheeloo" wrote:

Thanks for your feedback.
Here is the summary...
AK2: =IF(SUMPRODUCT(--(V2:Y2))=0,INDIRECT("E"&U2),"Not GWOT related")
U2: =MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)
V2: =ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0))
W2: =ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0))
X2: =OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0)))
Y2: =ISNA(MATCH(T2,H2:H33,0))

Formulae explained below are different from the above for your understanding..

Look for my answers below in the rows without an " "
If you are unable to make it work, then pl. send the file to me and I will
put in the formulae

You can put it in AK2.

Also I made a mistake...
In the formula
=IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related")
replace INDIRECT(U2) by INDIRECT("E"&U2) as U2 has the row no. where a match
occurs to get...
=IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT("E"&U2),"Not GWOT related")



Hope it works all the way...


(Assuming you are working on Row 2)
Enter this in U2
=MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)
Left(R2,2) will extract first two characters from R2, say AB
Then MATCH will look for *AB* in the range C2:C33 and return the row number
where match occurs
(AB is found anywhere in the range)
This will be used to find the corresponding entry in Col E if all conditions
are met
If no match is found it will return #N/A.

Formula Conditions:
If the left 2 characters of RCCC Code (Column R) match anything in Column C,
Rows 2-33 then continue, if not, then match is FALSE.
Enter this in V2
=ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0))
This will retrun TRUE or FALSE (when match is not found and #N/A is
returned. ISNA() checks for #N/A.
**I forgot to put a NOT() around it to get TRUE
so use
=NOT(ISNA(MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)))

If 3rd & 4th characters of RCCC Code (Column R) match the first two
characters in Column D, then continue, if not, then match is FALSE.
Enter this in W2
=ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0))
**Use =NOT(ISNA(MATCH(MID(R2,3,2))&"*",D2:D33,0)))

If you just want to look in D2 then replace D2:D33 with D2
MID extracts two characters from R2 starting at position 3 (which means 3rd
and 4th characters)

If PEC Code (Column S) matches PEC Code (either Column F or G) then
continue, if not, then match is FALSE.
Enter this in X2
=OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2:G33, 0)))

True if match is found either in F or G cols.
OR will be FALSE only if both match return FALSE
**USe
=NOT(OR(ISNA(MATCH(S2,F2:F33,0)),ISNA(MATCH(S2,G2: G33,0))))

If BAG Code (Column T) matches BAG Code (Column H) then continue, if not,
then match is FALSE.
Enter this in Y2
=ISNA(MATCH(T2,H2:H33,0))
**Use =NOT(ISNA(MATCH(T2,H2:H33,0)))

Last Step
Then put this in the Cell E2
=IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related")
SUMPRODUCT sums the number of TRUE in the range V2 to Y2. If all four have
TRUE then only you find for the match in Col E.

Copy these formulae down to the end of your dataset

Hope I have not made a mistake in copying the formula here... I hope you got
the idea...
You can always combine all these formulae into one cell

Let me know how it goes