Nested IF statements, or a better way?

[wizau]

Hello everyone,
Seeking some help for a project I'm working on. The scenario is approx 150x regional offices, that every year have an intake of 30-40 (sometimes up to 70) volunteers each office. These volunteers are entitled to a Uniform supplied by the organization. I am creating a spreadsheet that takes a volunteer's measurements (Head, Neck, Chest, Arm, Waist, Inseam, Foot - currently written down by hand on a form) and translates that into the available clothing sizes, and creates both the bulk order to the supplier, and each volunteer's 'Picking Card' when the clothing stock arrives. When the uniform stock arrives, we get a garbage bag with the volunteer's name and chuck all the uniform bits from the delivery boxes into the volunteer's named bag. This takes at least 2ppl 2x days as they sort through all the bits and use the paper Measurement Forms to translate in their head (or using the Size Matrixes) what size is needed to be put in the bag.

Yes, this system SUCKS!

Now that you have the background to understand the project, here is my request for assistance:

Attached for an example, are the sizing matrixes for the Long Sleeve Shirts & Trousers:
Shirts are Collar Size (Neck) and Sleeve Length (Arm) in cms. ie. 40/79. (The Short/Reg/Long 'fitments' are NOT labelled on the shirts or used to order, only the numbers.)
Trousers are labelled and ordered in Inches and the 'Fitment'. ie. 36Short, 38Long. (The cm measurements in the matrix are NOT accurate and are not labelled on the garment, or used.)

Just to explain the process I'm following in my head:
The pants are actually sized and labelled in inches, but everything else is sized in cm, so we take measurements in cm, as Australia is a Metric country.
Take the cm waist measurement from the volunteer, convert it to inches and CEILING to every 2nd inch between 26 & 46.
('sanity checked' using Data Validation of whole number between 60cm & 120cm on the measurement data entry point)
For each waist size, take the inseam measurement and select the available corresponding 'Fitment': X-Short/Short/Reg/Long/X-Long.
TEXTJOIN the CEILING inches waist value to the Fitment value & output as 'Trouser Size'.
Pivot Table all the different trouser sizes for every volunteer, to COUNT how many of each size needs to be ordered from the Supplier, and EXPAND to lookup what size are for which volunteers, to make picking & packing easier. But every garbage bag will have a card on it that lists what should be put in this bag, as a double check.

This is the code I have so far for the trousers.
But I've stuffed up somewhere as I added the 36-46in section for the trousers.
The first 26in section worked, as did the 28-34 section when I added it.
But I broke it and can't work out how to fix it when I added the 36-46 section.
I stayed up till 5:20am trying to sort it, before I resorted to ask for help here.

=IF(O2=26, ((IF(I2<71,"X-Short","Short")))
,
((IF(O2<35,
  (IF(I2<71,"X-Short",
   (IF(I2<76,"Short",
    (IF(I2<86,"Reg",
     (IF(I2<91,"Long",
      (IF(I2>90,"X-Long"
))))))))))))
,
((IFO2>35,
 (IF(I2<76,"X-Short",
  (IF(I2<81,"Short",
   (IF(I2<90,"Reg",
    (IF(I2<100,"Long",
    (IF(I2>99,"X-Long"
))))))))))))
)

Hopefully if you're still reading, you will see I'm not asking for people to do my work for me, I just need some help checking all the nested IF statements, as I had it working up until the last addition.
Or tell me there's a much better way to do this, (VLOOKUP??) and I will go and learn how to do that.

This nested IF statement method has worked fine for T-Shirts, Hats, Socks, Jackets, anything that doesn't have multiple 'fitment' sizes for each size, and are just generic Small/Med/Large/X-Large/XXL/XXXL.

Thanks for any assistance, as there's currently 144x Supply officers (who are also all volunteers on top of their day jobs!) that will thank you for making their lives so much easier every year at recruiting time.

Thank you.

 

[kvsrinivasamurthy]

Try this as replacement of nested IF formula.

=IF(O2=26, IF(I2<71,"X-Short","Short"),IF(O2<35,INDEX({"X-Short","Short","Reg","Long","X-Long"},MATCH(I2,{0,71,76,86,91,1000},1)),INDEX({"X-Short","Short","Reg","Long","X-Long"},MATCH(I2,{0,76,81,90,100,1000},1))))

 

[wizau]

Have never used INDEX & MATCH. Works much better, Thank you.

 

[petertenthije]

Using the shirts table as an example, you can use a combination of INDEX and MATCH formulas.

Assumptions made:

• Your table with shirt data can be found on Sheet1 cells A1 to P11.
• The entry form for the neck size is on Sheet2 cell A1.
• The entry form for the arm length is on Sheet2 cell A2.

When you use an INDEX formula, you have to enter three criteria. The data range you are looking in, the row number your desired data is in and the column number your desired data is in.

You know the data range you are looking for. It's shirts tables on Sheet1!A1:P11.
Let's start with what we know: =INDEX(Sheet1!$A$1:$P$11,ROWNUMBER,COLUMNNUMBER).

So you're missing the desired row number and column number.
The formula will need the row number first, but I will explain the column number first. It has no missing column headers making the explanation easier (the arm length skips sizes 71, 73, 75...).

To get the column number you can use a MATCH formula.
Select the range of cells in which you want to find a value.
In this case you want to know where in Sheeet1!A1:P1 the neck size entered in cell Sheet2!A1 can be found.
That's done using: =MATCH(Sheet1!$A$1,Sheet2!$A$1:$P$1,0)
This formula returns the column number, so for size 31 the answer would be 3 (cell Sheet1!C1), for size 32 the anwer would be 4 (cell Sheet1!D1).

Add that to the INDEX formula created earlier:
=INDEX(Sheet1!$A$1:$P$11,ROWNUMBER,MATCH(Sheet1!$A$1,Sheet2!$A$1:$P$1,0)).

For the row number (arm length), you are missing values so a straight match like for the neck sizes won't work. You need the closest match greater then entered.
For that you can use this formula: =MATCH(TRUE,INDEX(Sheet1!$A$3:$A$11>=Sheet2!$A$2,0),)+2
It uses a MATCH formule to find the closest match, then an INDEX formula to return the row number of the closest match.
Note that this should only look at the numbers! That's why the INDEX formula does not look at Sheet1!$A$1:$A$11, but at Sheet1!$A$3:$A$11.
To make up for that at the end of the formula a +2 has been added.

Add that to the INDEX formula created earlier:
=INDEX(Sheet1!$A$1:$P$11,MATCH(TRUE,INDEX(Sheet1!$A$3:$A$11>=Sheet2!$A$2,0),)+2,MATCH(Sheet2!$A$1,Sheet1!$A$1:$P$1,0))

 

[wizau]

Yes, Thank you. I'm already modifying the provided information for the 'WORK Shirts" which only have Chest measurement of 5cm increments & Short/Reg/Long fitments.
I'll follow your example and put the Long Sleeve Shirt data in their own table, rather than programming into the formula. I know this is better practice in case we change shirt suppliers and need to alter the sizes, we only need to edit the table, not every formula.

 

[wizau]

Sorry, back again. I have successfully been using the INDEX MATCH functions for my clothing spreadsheet, and I'm down to the socks.
I have the following formula that checks for a 'mm' measurement of the length of a foot from CadetInfo!K2 (where all the raw measurement data is) and estimates the shoe size required. L2 is a shoe size (5, 7.5, 10, etc) as an alternative measurement method.

Cell Z2 Formula:

=IF(ISBLANK(CadetInfo!K2),CadetInfo!L2,(INDEX({"3","3.5","4","4.5","5","5.5","6","6.5","7","7.5","8","8.5","9","9.5","10","10.5","11","11.5","12","12.5","13"},
MATCH(K2,{228,232,236,240,244,248,252,256,260,264,268,272,276,280,284,288,292,296,300,304},1))))

That all works fine. I get the proper shoe size from that formula.
From that shoe size, I want to get the sock size required (4x sizes: Small, Med, Lrg, XLrg).
But no matter if I try to use an Index Match or a nested IF statement, it won't evaluate.
Even a simple IF statement referencing Z2 with the INDEX MATCH result doesn't evaluate?
It returns Medium, no matter what value is displayed in Z2 from the INDEX MATCH formula.
I'll try and upload a mini-Sheet in a sec. My laptop wants to reboot from updates and my mouse is doing wierd things.
I've also just thought about using another INDEX MATCH referencing CadetInfo!K2 rather than the shoe size, but the socks are 'sized' according to shoe size, so it just makes sense to do that. But if I have to, I guess I can use the same INDEX MATCH formula for the Shoe size and replace with sock sizes? Just seems stupid to me.

=IF(Z2<7,"Small","Medium") ALWAYS returns Medium, no matter the value in Z2!

 

[wizau]

Book1
	A	B	C	D	E	F
1	Foot	Boots	Socks(Index)	Socks(IF)	Socks(A2 Index)	Socks(A2 IF)
2	230mm	3	#N/A	XLarge	Small	Small
Sheet1

Cell Formulas
Range	Formula
B2	B2	=INDEX({"3","3.5","4","4.5","5","5.5","6","6.5","7","7.5","8","8.5","9","9.5","10","10.5","11","11.5","12","12.5","13"}, MATCH(A2,{228,232,236,240,244,248,252,256,260,264,268,272,276,280,284,288,292,296,300,304,308},1))
C2	C2	=INDEX({"Small","Medium","Large","X-Large"},MATCH(B2,{0,6,9,11,13},1))
D2	D2	=IF(B2<7,"Small",IF(B2<10,"Medium",IF(B2<12,"Large","XLarge")))
E2	E2	=INDEX({"Small","Medium","Large","X-Large"},MATCH(A2,{0,245,275,300,350},1))
F2	F2	=IF(A2<256,"Small",IF(A2<275,"Medium",IF(A2<300,"Large","XLarge")))

Cells with Data Validation
Cell	Allow	Criteria
A2	Whole number	between 228 and 320

E2 & F2 work, referencing A2. But C2 & D2 referencing B2 don't.