Excel Combine IF(OR with IF(AND. Is it better to do it in 3 stages or it all at once? 1

[PWD]

Good afternoon. I have been supplied data which cover 4 types of industry with 3 sizes of company. I have to allocate a numeric 'type' to each record based on this 4 x 3 grid, 1 to 12. However within the data are different way of representing either the type or the size, e.g.

"Business and Finance", "Finance and Business"
"Large", "Large 250+"

So I decided that a relatively simple approach would be to create 3 x formulae:

R2
=IF(OR(B2="Business and Finance",B2="Finance and Business",B2="Finance and Business Services",B2="Finance/Business",B2="Finance/business services"),"BF",IF(OR(B2="Transport and Distribution",B2="Transport/Distribution"),"TD",IF(OR(B2="Retail and Wholesale",B2="Retail/wholesale",B2="Wholesale",B2="Wholesale and Retail",B2="Wholesale/Retail"),"WR","MC")))

S2
=IF(OR(C2="Large",C2="Large 250+"),"L",IF(OR(C2="Medium",C2="Medium 50-249"),"M","S"))

T2
=IF(AND(R2="MC",S2="L"),"9",IF(AND(R2="MC",S2="M"),"5",IF(AND(R2="MC",S2="S"),"1",IF(AND(R2="WR",S2="L"),"10",IF(AND(R2="WR",S2="M"),"6",IF(AND(R2="WR",S2="S"),"2",IF(AND(R2="TD",S2="L"),"11",IF(AND(R2="TD",S2="M"),"7",IF(AND(R2="TD",S2="S"),"3",IF(AND(R2="BF",S2="L"),"12",IF(AND(R2="BF",S2="M"),"8",IF(AND(R2="BF",S2="S"),"4",""))))))))))))

Then I wondered if there was any mileage in just doing it in one step so set about trying to build a suitable formula. This didn't seem so bad for the multiple spellings of one type and just one spelling of one size:

=IF(OR(AND(B2="Business and Finance", C2="Large"), AND(B2="Finance and Business", C2="Large"), AND(B2="Finance and Business Services", C2="Large"), AND(B2="Finance/Business", C2="Large"), AND(B2="Finance/business services", C2="Large")),"12","Wrong")

But then I got a bit stuck in trying to accomodate the other variation of size. And frankly this strikes me as ridiculous:

=IF(OR(AND(B2="Business and Finance",C2="Large"),AND(B2="Finance and Business",C2="Large"),AND(B2="Finance and Business Services",C2="Large"),AND(B2="Finance/Business",C2="Large"),AND(B2="Finance/business services",C2="Large")),"12",IF(OR(AND(B2="Business and Finance",C2="Large 250+"),AND(B2="Finance and Business",C2="Large 250+"),AND(B2="Finance and Business Services",C2="Large 250+"),AND(B2="Finance/Business",C2="Large 250+"),AND(B2="Finance/business services",C2="Large 250+")),"12","Wrong"))

I guess if I could also incorporate another "OR" to cover "Large/Large 250+" it might help but would it be worth it?

Many thanks,
D€$

 

[SkipVought]

hi,

I'd build a TABLE to cross-reference the possibilities.

It is MUCH easier to maintain data in a table and reference via a simple lookup function, than to write complex nested expressions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I'm going to try this tomorrow - I've already made the table - with INDEX MATCH.

Many thanks,
D€$

 

[PWD]

This appears to do what I want:

=VLOOKUP(B2,Table!A:G,MATCH(C2,Table!$1:$1,0),0)

Many thanks,
D€$