Look Up Next Size Door

[Val_J]

I have a single column which I list all the door sizes offered. Doors are listed by width and then height.

Example of Door Sizes Offered Column (info for clarity)
3068 (3'-0" x 6'-8")
3070 (3'-0" x 7'-0")
4040 (4'-0" x 4'-0")
4068 (4'-0" x 6'-8")
4070 (4'-0" x 7'-0")
5068 (5'-0" x 6'-8")
5070 (5'-0" x 7'-0")
*Actual List is about 300 sizes

User Inputs - Each their own cell
Width in Feet
Width in Inches
Height in Feet
Height in Inches

If Door Size input by user does not exist I want to select the next size door with equal or greater width and height. I was going to use next larger number but this does not always out door with larger height since second variable. Example client wants a 3868 the next largest number is a 4040 so found a good width but the height is a problem.

I was hoping to find a formula solution and stay away from a custom vba function. Any help would be great.

I can break out the width and height into their own column and place side by side if this helps.

 

[Lewiy]

I presume that feet and inches can both go over 9, so how do you display, for example, 11'10"x5'11"? Would it be 1110511?

 

[Val_J]

Every value is given two digit placemarks so their is a consistant pattern,I was just trying to keep simple
11'10"x5'11"
11100511

 

[mikerickson]

The data in the OP example does not match the description in post#3.
The OP says "in a single column", does that mean that the style number is in the same cell as the dimensions?
Can you give an example of one cell's entry?
Is the value in a cell numeric with the ( '- "x '- ") added by a custom format?

 

[Lewiy]

So the sizes must be in text format then to account for leading zeros on sizes under 10 feet high?

 

[Val_J]

Typical User Input
Columns
A B C D E F G
<TABLE style="WIDTH: 140pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=182 border=0 x:str><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" span=7 width=26><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=78 colSpan=3 height=19>Width
</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 20pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: transparent" width=26 rowSpan=2></TD><TD class=xl34 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78 colSpan=3>Height</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ft.</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">in.</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">ft.</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">-</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">in.</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="3">3'</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(A3>0,"-","")'>-</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="6">6''</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(A3>0,"x","")'>x</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="6">6'</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla='=IF(E3>0,"-","")'>-</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="9">9''</TD></TR></TBODY></TABLE>

A Custom format is used to display the feet and inches symbols.

The single column of door sizes is actually
03000608
03000700
04000400
04000608
04000700
05000608
05000700

 

[Val_J]

I have not dealt with leading zero matter yet, thought the solution or adivse here would steer that decision.

 

[Lewiy]

I mean, you have leading zeros in your single column of door sizes:
03000608
03000700
04000400
04000608
04000700
05000608
05000700
What format are these cells?

 

[Val_J]

Sorry I have flexibility here I can make it whatever works best to get solution.

I can place an astrick in front to display leading 0 thus always 8 places or whatever is required to get the most simple solution. I can even change format placing a symbol between feet and inches

 

[mikerickson]

Perhaps something like this.
Column A has numbers representing door sizes
(the formatting to show the leading 0 is irrelevant to the solution and can be changed to meet your needs)
Column C has the sizes sought.
Column E has the row number of the matching door. The formula in E1 is
=MATCH(1, (INT(C1/10000)<=INT($A$1:$A$9/10000))*(MOD(C1,10000)<=MOD($A$1:$A$9,10000)), 0)
Entered with Ctrl-Shift-Enter (Cmd+Return for Mac) and dragged down.

Column F has the matching door size, the formula =INDEX(A:A,E1,1)

Note that there are no 8 foot high doors, so E4/F4 show an error.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="right" bgcolor=#FFFFFF>03000608<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>03000600<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>03000608</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="right" bgcolor=#FFFFFF>03000700<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>03000700<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>03000700</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="right" bgcolor=#FFFFFF>04000400<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>03060609<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>5<td align="right" bgcolor=#FFFFFF>04000700</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="right" bgcolor=#FFFFFF>04000608<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>03000800<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>#N/A<td align="left" bgcolor=#FFFFFF>#N/A</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="right" bgcolor=#FFFFFF>04000700<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>04000611<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>5<td align="right" bgcolor=#FFFFFF>04000700</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="right" bgcolor=#FFFFFF>05000608<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="right" bgcolor=#FFFFFF>05000700<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>