Multiple Nested Function

[Russ]

I'm attempting to assign contract descriptions based on multiple elements of
the contracts. All data is in the same worksheet / same tab. I could use
some help developing a series of nested functions that compare data and pull
the correct description. Here's what I've got to work with:

Formula Objective:
Search the combination of Contract RCCC, PEC, and BAG codes and select the
applicable description if all elements are TRUE. If any of the elements are
FALSE, enter “Not GWOT relatedâ€.

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.
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.
If PEC Code (Column S) matches PEC Code (either Column F or G) then
continue, if not, then match is FALSE.
If BAG Code (Column T) matches BAG Code (Column H) then continue, if not,
then match is FALSE.

Contract Codes:
RCCC Code Column R; Rows 2-4804, inclusive
PEC Code Column S; Rows 2-4804, inclusive
BAG Code Column T; Rows 2-4804, inclusive

Criteria:
6 Digit RCCC Code Column A; Rows 2-33, inclusive
RCCC Digits 1 & 2 Column C; Rows 2-33, inclusive
RCCC Digits 3 to 6 Column D; Rows 2-33, inclusive
PEC Column F or Column G; Rows 2-33, inclusive
BAG Column H; Rows 2-33, inclusive

Desired Description Column E; Rows 2-33, inclusive

 

[Sheeloo]

Hello Russ,

The detailed description provided by you was excellent...
Look for my answers below in the rows without an ">"

(Assuming you are working on Row 2)
Enter this in U2
=MATCH("*"&LEFT(R2,2)&"*",C2:C33,0)

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))

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))&"*",D233,0))
If you just want to look in D2 then replace D233 with D2

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)))

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))

Last Step
Then put this in the Cell E2
=IF(SUMPRODUCT(--(V2:Y2))=4,INDIRECT(U2),"Not GWOT related")
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

 

[Russ]

Hi Sheeloo,

I appreciate your advise; I was surprised at how quickly I received an answer.

Could you explain what each of your formulas do? I'm not familiar with some
of the variables you used and I don't quite understand how they relate to the
array's being addressed.

Also, on the last step you noted that I should put that function in cell E2.
That's where my first RCCC description is located; I was hoping to put the
final nested function on the right side of my spreadsheet (which would be
column AK) and have it pull the RCCC description from E2:E33.

Again, I thank you for your help. You've got me a lot closer than I've been
able to do on my own.

Russ

 

[Sheeloo]

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))&"*",D233,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...

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.
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)))
**Use =NOT(ISNA(MATCH(MID(R2,3,2))&"*",D233,0)))
MID extracts two characters from R2 starting at position 3 (which means 3rd
and 4th characters)

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))))
**Use =NOT(ISNA(MATCH(T2,H2:H33,0)))
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.

 

[Russ]

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 (e-mail address removed).

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

 

[vezerid]

I think...

Having read the thread and reread your initial post from yesterday
that the following is the formula you need to use in AK2 and copy
through to AK4804.

=INDEX($E$2:$E$33,MATCH(1,(LEFT(R2,2)=$C$2:$C$33)*(MID(R2,3,2)=LEFT($D
$2:$D$33,2))*((S2=$F$2:$F$33)+(S2=$G$2:$G$33))*(T2=$H$2:$H$33),0))

This is an *array* formula. In AK2 commit with Shift+Ctrl+Enter. Then
copy down.

HTH
Kostis Vezerides