Complex Nesting

[logstx095]

Hello. I have a spreadsheet with numerous (hundereds) of naming conventions
in a field that I need to search. One common denominator, though, is that
they will always contain text including either "BK", "SL", "OF", or "N". By
using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"), this
almost accomplishes what I need, except that there are the 4 conditions, not
just two.
I need to have a formula test for the text BK, and if that exists, return
the word BULK, if BK is not contained in the cell, the formula should test
for the text SL; if SL is contained in the cell, the return should be
SIDELOAD, thirdly, if the text SL is not found in the cell, the formula
should test for the text N; if N is found, the formula should return NIGHT
SIDELOAD; and finally, if none of the prior 3 conditions is met, the formula
should return OFS.
I should be able to use the above formula, but I can't seem to get the
syntax correct to nest it all together.
Any help is much appreciated.

[VBA Noob]

This works but not very neat

=IF(A1="BK","Bulk","")&IF(A1="SL","Sideload","")&IF(A1="N","Night","")&IF(A1="N","Night","")&IF(AND(A1<>"BK",A1<>"SL",A1<>"N",A1<>""),"OFS","")

[logstx095]

Hello there,

Thanks much for the advice. Will that work for my instance, where the
whole cell contents will contain more than just BK, SL, OF, and N? The cells
actually contain text including those letters, but the whole contents are
similar to ABK01, ABK02 up through potentially ABK99 daily, PBK01, PBK02
potentially up to PBK99 daily, and the same for the Sideload, OFS and Nights,
being ASL01 through ASL99, AOF01 through AOF99, ANS01 through ANS99, and
other text combos for 44 sales centers up to 99 routes daily. Each route is
named using a combination of the abbreviation for the location, the
abbreviation for the route type, and a two digit route number from 01 to 99.
That's why I was thinking I had to do a search just for the common text,
since there are potentially 396 combinations for any given sales center, and
44 sales centers, adding up to 17,424 possible combinations, but the common
denominator is that they will all contain the text BK, SL, OF, or N in the
names.


"VBA Noob" wrote:

>
> This works but not very neat
>
> =IF(A1="BK","Bulk","")&IF(A1="SL","Sideload","")&IF(A1="N","Night","")&IF(A1="N","Night","")&IF(AND(A1<>"BK",A1<>"SL",A1<>"N",A1<>""),"OFS","")
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=560866
>
>

[Biff]

Hi!

Try this:

Make a little table somewhere like this: (I'll use the range G1:H4 for this
example)

.........G...............H
1.....BK............bulk
2.....SL.............sideload
3.....N..............night sideload
4.....OF............OFS

Then use this array formula entered using the key combination of
CTRL,SHIFT,ENTER:

=CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1:G$4&"*",A1)),0),H$1,H$2,H$3,H$4)

Biff

"logstx095" <[email protected]> wrote in message
news:[email protected]...
> Hello. I have a spreadsheet with numerous (hundereds) of naming
> conventions
> in a field that I need to search. One common denominator, though, is that
> they will always contain text including either "BK", "SL", "OF", or "N".
> By
> using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"),
> this
> almost accomplishes what I need, except that there are the 4 conditions,
> not
> just two.
> I need to have a formula test for the text BK, and if that exists, return
> the word BULK, if BK is not contained in the cell, the formula should test
> for the text SL; if SL is contained in the cell, the return should be
> SIDELOAD, thirdly, if the text SL is not found in the cell, the formula
> should test for the text N; if N is found, the formula should return NIGHT
> SIDELOAD; and finally, if none of the prior 3 conditions is met, the
> formula
> should return OFS.
> I should be able to use the above formula, but I can't seem to get the
> syntax correct to nest it all together.
> Any help is much appreciated.

[Biff]

Or:

=INDEX(H$1:H$4,MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1:G$4&"*",A1)),0))

Also array entered.

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> Make a little table somewhere like this: (I'll use the range G1:H4 for
> this example)
>
> ........G...............H
> 1.....BK............bulk
> 2.....SL.............sideload
> 3.....N..............night sideload
> 4.....OF............OFS
>
> Then use this array formula entered using the key combination of
> CTRL,SHIFT,ENTER:
>
> =CHOOSE(MATCH(TRUE,ISNUMBER(SEARCH("*"&G$1:G$4&"*",A1)),0),H$1,H$2,H$3,H$4)
>
> Biff
>
> "logstx095" <[email protected]> wrote in message
> news:[email protected]...
>> Hello. I have a spreadsheet with numerous (hundereds) of naming
>> conventions
>> in a field that I need to search. One common denominator, though, is that
>> they will always contain text including either "BK", "SL", "OF", or "N".
>> By
>> using the formula =IF(ISNUMBER(A1),LOOKUP("BK",A1)),"BULK", "SIDELOAD"),
>> this
>> almost accomplishes what I need, except that there are the 4 conditions,
>> not
>> just two.
>> I need to have a formula test for the text BK, and if that exists, return
>> the word BULK, if BK is not contained in the cell, the formula should
>> test
>> for the text SL; if SL is contained in the cell, the return should be
>> SIDELOAD, thirdly, if the text SL is not found in the cell, the formula
>> should test for the text N; if N is found, the formula should return
>> NIGHT
>> SIDELOAD; and finally, if none of the prior 3 conditions is met, the
>> formula
>> should return OFS.
>> I should be able to use the above formula, but I can't seem to get the
>> syntax correct to nest it all together.
>> Any help is much appreciated.
>
>