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.

 

[shg]

Another way:

       ---A---- B- C- ------------------D-------------------
   1     P/N    W  H                                        
   2            29 85 B2:C2: Input                          
   3   02060706 30 90 B3: =VLOOKUP(A3, $A$8:$D$42, 2, FALSE)
   4                  C3: =VLOOKUP(A3, $A$8:$D$42, 3, FALSE)
   5                                                        
   6                                                        
   7     P/N    W  H                                        
   8   02000600 24 72                                       
   9   02000609 24 81                                       
  10   02000700 24 84                                       
  11   02000706 24 90                                       
  12   02000800 24 96                                       
  13   02020600 26 72                                       
  14   02020609 26 81                                       
  15   02020700 26 84                                       
  16   02020706 26 90                                       
  17   02020800 26 96                                       
  18   02040600 28 72                                       
  19   02040609 28 81                                       
  20   02040700 28 84                                       
  21   02040706 28 90                                       
  22   02040800 28 96                                       
  23   02060600 30 72                                       
  24   02060609 30 81                                       
  25   02060700 30 84                                       
  26   02060706 30 90                                       
  27   02060800 30 96                                       
  28   02080600 32 72                                       
  29   02080609 32 81                                       
  30   02080700 32 84                                       
  31   02080706 32 90                                       
  32   02080800 32 96                                       
  33   02100600 34 72                                       
  34   02100609 34 81                                       
  35   02100700 34 84                                       
  36   02100706 34 90                                       
  37   02100800 34 96                                       
  38   03000600 36 72                                       
  39   03000609 36 81                                       
  40   03000700 36 84                                       
  41   03000706 36 90                                       
  42   03000800 36 96

The ugly formula in A3 is

=INDEX(A8:A42, MATCH(MIN(IF(B8:B42>=B2, IF(C8:C42>=C2, B8:B42*C8:C42))), IF(B8:B42>=B2, IF(C8:C42>=C2, B8:B42*C8:C42))))

... confirmed with CSE.

It chooses the door with the minimum area having width and height >= the required size.

 

[Val_J]

I am not sure I understand your solution but it works great. I think I need to polish my array formula skills. Thanks again and have a great day.

 

[hiker95]

shg4421, nicely done.


Val_J, this may help.

Excel Workbook
	A	B	C
1	P/N	W	H
2		29	68
3	02060600	30	72
4
5
6
7	P/N	W	H
8	02000600	24	72
9	02000609	24	81
10	02000700	24	84
11	02000706	24	90
12	02000800	24	96
13	02020600	26	72
14	02020609	26	81
15	02020700	26	84
16	02020706	26	90
17	02020800	26	96
18	02040600	28	72
19	02040609	28	81
20	02040700	28	84
21	02040706	28	90
22	02040800	28	96
23	02060600	30	72
24	02060609	30	81
25	02060700	30	84
26	02060706	30	90
27	02060800	30	96
28	02080600	32	72
29	02080609	32	81
30	02080700	32	84
31	02080706	32	90
32	02080800	32	96
33	02100600	34	72
34	02100609	34	81
35	02100700	34	84
36	02100706	34	90
37	02100800	34	96
38	03000600	36	72
39	03000609	36	81
40	03000700	36	84
41	03000706	36	90
42	03000800	36	96
43
32