Shirt Size with 2x Conditions (Chest & Height)

[wizau]

I thought I'd found the answer here: Shirt Sizing with Two Conditions
But the HTML code is not being executed by my browser and I can't read the thread.

And wondering if there's a better way since 10 years ago!

This spreadsheet is going out to 144 regional non-profit, non-religious, non-political youth leadership groups, for the kid's uniform sizing.
I'd really appreciate the help. Next year's intake is getting very close. (late November)

For (nearly) every shirt chest size in cm, there is a corresponding 'fitment' of Short / Reg / Long based on the person's height.
65 Short
65 Reg
65 Long
or...
70S, 70R, 70L
75S, 75R, 75L

Here's the 'logic' I need converted into a Formula and my first crack at it:
C2 is the raw measurement of the person's chest and H2 is the raw measurement of their height.
Chest size is primary "Must" fit, fitment length is secondary.
Everything is in whole numbers and conditionally limited to what can only be 'real world' numbers to avoid data entry mistakes.

=IF(AND(C2<="75",H2<="160"),CONCAT(CEILING(C2, 5),"S"), else...)
=IF(AND(AND(C2>="75",C2<="99"),H2<="170",CONCAT(CEILING(C2, 5),"S", else...)
=IF(AND(C2>="91",H2<="180"),CONCAT(CEILING(C2, 5),"S"), else...)

=IF(AND(C2<="75",H2<="170),CONCAT(CEILING(C2, 5),"R"))
If C2=76-85 & H2<181 =CONCAT(CEILING(C2, 5),"R")
If C2 = 86-110 & H2<191 =CONCAT(CEILING(C2, 5),"R")
=IF(AND(C2>110,H2<="200"),CONCAT(CEILING(C2, 5),"R"))

If C2 = <74 & H2>170 = Long
If C2 = 75-80 & H2>180 = Long
If C2 = 81-105 & H2>190 = Long
If C2 >105 & H2>200 = Long
(or... Else =CONCAT(CEILING(C2, 5),"L") .....after Short & Reg conditionals exhausted must be Long??)

I tried to see if it was a straight ratio that I could divide height by chest or chest by height and use the ratio to choose fitment, but it's not.
Size matrix raw data is attached.

Once I have an example to work from I's sure I will be able to translate it into the trouser sizes which are a similar waist & fitment length options, but XX-Short and X-Long as well.

I really can't believe there's not already something like this somewhere for me to use an an example, but I couldn't find one, and I'm not good enough to write nested / cascading IF statement formula. And as said, can't help thinking there could be a better way. I have real trouble with very long nested IF statement formulas!!

Thank you so much for any assistance. And I'm sure for others trying to do the same thing as well.

 

[myall_blues]

And wondering if there's a better way since 10 years ago!

Likely there is. What version of Excel are you using? It's helpful if you update your profile with that information.

Also what is the expected result? For example if the chest size is 65 and height is 165 then I assume you want 65R as the result?
What happens if the chest size is between two sizes - for example 68? Do you round up?

 

[wizau]

Likely there is. What version of Excel are you using? It's helpful if you update your profile with that information.

Also what is the expected result? For example if the chest size is 65 and height is 165 then I assume you want 65R as the result?
What happens if the chest size is between two sizes - for example 68? Do you round up?

Hello Murray,
What version of Excel I am using is irrelevant. There are 144 different users, all on different versions.
Some will have corporate supplied laptops, some will have Government on older versions, and some be using their own retail purchased.
Some may even have a Mac and using Numbers. Not my problem to support those.
But just for the sake of it:
Microsoft® Excel® 2021 MSO (Version 2409 Build 16.0.18025.20030) 64-bit

Yes, the size is rounded up. Hence the CONCAT(CEILING(C2, 5),"S")

You have read the table correctly. Chest of 65 & Height of 165 would be a 65 Reg.
As another example, chest of 92 and height of 198 would be a 95 Long.

 

[myall_blues]

OK. Knowing we're dealing with virtually any version is helpful. The formula here is pretty much version proof I think.

How about this using a reference table. In a situation such as yours complex nested IFs are best avoided. If you wanted you could put the reference table on a different sheet and hide it (or even make it "very hidden" if you like).

Note: The chest sizes in the reference table must be in descending order.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Chest					Height	Fitting					Chest	Height
2			65					165	65R						140	150	160	170	180	190	200
3			92					198	95L					130			S	S	R	R	L
4														125			S	S	R	R	L
5														120			S	S	R	R	L
6														115			S	S	R	R	L
7														110			S	S	R	R	L
8														105			S	S	R	L	L
9														100			S	S	R	L	L
10														95		S	S	R	R	L	L
11														90		S	S	R	R	L	L
12														85		S	S	R	R	L	L
13														80		S	S	R	L	L	L
14														75	S	S	S	R	L	L	L
15														70	S	S	R	L	L	L	L
16														65	S	S	R	L	L	L	L
17														60	S	S	S
Sheet2

Cell Formulas
Range	Formula
I2:I3	I2	=INDEX($N$3:$N$17,MATCH($C2,$N$3:$N$17,-1))&INDEX($O$3:$U$17,MATCH(INDEX($N$3:$N$17,MATCH($C2,$N$3:$N$17,-1)),$N$3:$N$17,0),MATCH($H2,$O$2:$U$2,1))

 

[Peter_SSs]

What version of Excel I am using is irrelevant.

It may be for this question but perhaps not always, so I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). Besides, it will stop people regularly requesting your version details.

Then in any particular question that you ask, if the solution needs to work in different versions put that detail in post #1 of the thread.

Also, for the future, you can encourage more people to help if you provide them with sample data that they can easily copy & use for testing - unlike just an image.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that @myall_blues has used XL2BB and you can easily see what columns & rows things are in, what the formulas are and where they are and, if you want, can copy the whole thing (including formulas) to a worksheet to test using the copy icon at the top left of the mini sheet.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[wizau]

It may be for this question but perhaps not always, so I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’). Besides, it will stop people regularly requesting your version details.

Then in any particular question that you ask, if the solution needs to work in different versions put that detail in post #1 of the thread.

Also, for the future, you can encourage more people to help if you provide them with sample data that they can easily copy & use for testing - unlike just an image.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that @myall_blues has used XL2BB and you can easily see what columns & rows things are in, what the formulas are and where they are and, if you want, can copy the whole thing (including formulas) to a worksheet to test using the copy icon at the top left of the mini sheet.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

All points acknowledged, for future posts, & actioned where possible.

 

[wizau]

OK. Knowing we're dealing with virtually any version is helpful. The formula here is pretty much version proof I think.

How about this using a reference table. In a situation such as yours complex nested IFs are best avoided. If you wanted you could put the reference table on a different sheet and hide it (or even make it "very hidden" if you like).

Note: The chest sizes in the reference table must be in descending order.

Thank you Murray,

I am trying to become more knowledgeable with INDEX & MATCH.
I have use an INDEX MATCH formula with one measurement already, but wasn't aware how to do it with two measurements.
And yes, I do already have a Hidden sheet with all the Lists and Names on, so no problem there.

 

[myall_blues]

You’re welcome. I hope it helps.

 

[Peter_SSs]

All points acknowledged, for future posts, & actioned where possible.

Thanks for updating your profile.