less than greater than for large amount of data

[excelnoob123456]

Hi Guys

I am OK with Excel and I know a way that I can do this, using If and AND formulae, but I feel there must be a neater way to do this given the large amount of data I'm working with would make my formula a bit unwieldy.

See example data below:

	A	B	C	E	F	G	H	I	J
1	Type	SizeA	SizeB		222 (sizeA)	222 (sizeB)		555 (size a) <tbody> </tbody>	555 (size b)
2	222 <tbody> </tbody>	1436 <tbody> </tbody>	2725		2000 <tbody> </tbody>	1500		1200 <tbody> </tbody>	1500
3	234 <tbody> </tbody>	1303 <tbody> </tbody>	1984		2000 <tbody> </tbody>	2100		1300 <tbody> </tbody>	1700
4	607 <tbody> </tbody>	1237 <tbody> </tbody>	1953		2000 <tbody> </tbody>	2550		1500 <tbody> </tbody>	1900
5	555 <tbody> </tbody>	1224 <tbody> </tbody>	2075		2550 <tbody> </tbody>	1500		1500	1200
6					2550 <tbody> </tbody>	2100		1600	1300

<tbody>
</tbody>

Now, in my data all of the types (eg. 222,234,607,etc) have their own sizes as shown, but I want to return the 'allowable size' from the tables to the right, all of the types have their own different allowable sizes and sometimes the values they have can be outside of the allowable range, in which case I would like to display "width too large" for example. Now, in reality the SizeA and B are both in the same cell eg. 1224x2075 for both sets of data, and I think I will add columns where I seperate this out to make the formula easier. So currently my thinking would be to do something like:

=IF(and(A2=F1,b2<=f2,c2<=g2),concatenate(f2&"x"&g2),if(and(a2=f1,b2<=f2,c2>g2,c2<=g3),concatenate(f2&"x"&g3),etc.(

Going through the who line of options this would be quite a cumbersome formula, I think there must be an easier way to do this but I'm not sure how?

Any help greatly appreciated.

 

[StephenCrump]

Can you provide some more examples to illustrate, and also show the results you're expecting?

I'm guessing that if you had a 222 with dimensions 1800 x 1400, you'd return "2000x1500"?

What about a 222 with dimensions 1400 x 1400 where the allowable sizes include 1500x2000 and 1800x1600? Do you first choose the smallest A that will fit and return 1500x2000, or first choose the smallest B that will fit and return 1800X1600?

etc etc ...

For the 222 and 555 examples posted, both B sizes exceed the ranges available, so presumably you'll want to say "Size B too large"?

 

[excelnoob123456]

Hi Stephen

Thanks for the reply. I will try to clarify what I'm trying to do a bit better...

I need to return the allowed values which are either bigger of the same size as the actual values, in the case where there is more than one acceptable size I would like the option with the smallest total area (sizea multiplied by sizeb). I only want the error displayed if there are no allowable sizes which will fit the actual size.

I should clarify as well that the Size A and Size B are linked, e.g. for a piece of wood, so if the actual size they want is a 1500x1234 (sizea x sizeb) I need to find the nearest size that would be bigger or as big as that overall. So for my example, I am comparing the sizes in b2 and c2, which is type 222 (cell a2) to the allowable sizes for a type 222 in f2:g6, so I would check first if b2 is less than or equal to f2 AND c2 is less than or equal to g2, say b2 is but c2 isn't, then I would check c2 against g3, g4 etc. and same with c2 if it didn't match.

I know that this will work but there are more tables than this so there are quite a large number of possible variations which would make the formula quite big, so if there was another option that would be excellent.

Hope this makes sense, it's hard to explain when writing.

 

[StephenCrump]

Perhaps something like this ...

I have used a helper column, so that the formula isn't twice as long.

Not checked in great detail yet, so see if you can break it!

D3 (array-entered): =MATCH(MIN(IF((INDEX(Sizes,,MATCH(A3,Headers,))>=B3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=C3)+(INDEX(Sizes,,MATCH(A3,Headers,))>=C3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=B3),INDEX(Sizes,,MATCH(A3,Headers,))*INDEX(Sizes,,1+MATCH(A3,Headers,)))),IF((INDEX(Sizes,,MATCH(A3,Headers,))>=B3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=C3)+(INDEX(Sizes,,MATCH(A3,Headers,))>=C3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=B3),INDEX(Sizes,,MATCH(A3,Headers,))*INDEX(Sizes,,1+MATCH(A3,Headers,))),)

E3: =INDEX(Sizes,D3,MATCH(A3,Headers,))&"x"&INDEX(Sizes,D3,1+MATCH(A3,Headers,))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							222	222	234	234	555	555	607	607
2	Type	SizeA	SizeB	Match	Result?		SizeA	SizeB	SizeA	SizeB	SizeA	SizeB	SizeA	SizeB
3	222	1436	2050	4	2550x1500		2000	1500	1200	1500	1200	1500	1200	1500
4	234	1303	1700	3	1500x1900		2000	2100	1300	1700	1300	1700	1300	1700
5	607	1237	1450	5	1600x1300		2000	2550	1500	1900	1500	1900	1500	1900
6	555	1224	1500	5	1600x1300		2550	1500	1500	1200	1500	1200	1500	1200
7	607	1450	1237	5	1600x1300		2550	2100	1600	1300	1600	1300	1600	1300
8	607	1237	1450	5	1600x1300
9	607	1237	1950	#N/A	No can do!
Sheet1

 

[StephenCrump]

... where

Headers: G1:N1
Sizes: G3:N7

 

[excelnoob123456]

Perhaps something like this ...

I have used a helper column, so that the formula isn't twice as long.

Not checked in great detail yet, so see if you can break it!

D3 (array-entered): =MATCH(MIN(IF((INDEX(Sizes,,MATCH(A3,Headers,))>=B3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=C3)+(INDEX(Sizes,,MATCH(A3,Headers,))>=C3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=B3),INDEX(Sizes,,MATCH(A3,Headers,))*INDEX(Sizes,,1+MATCH(A3,Headers,)))),IF((INDEX(Sizes,,MATCH(A3,Headers,))>=B3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=C3)+(INDEX(Sizes,,MATCH(A3,Headers,))>=C3)*(INDEX(Sizes,,1+MATCH(A3,Headers,))>=B3),INDEX(Sizes,,MATCH(A3,Headers,))*INDEX(Sizes,,1+MATCH(A3,Headers,))),)

E3: =INDEX(Sizes,D3,MATCH(A3,Headers,))&"x"&INDEX(Sizes,D3,1+MATCH(A3,Headers,))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							222	222	234	234	555	555	607	607
2	Type	SizeA	SizeB	Match	Result?		SizeA	SizeB	SizeA	SizeB	SizeA	SizeB	SizeA	SizeB
3	222	1436	2050	4	2550x1500		2000	1500	1200	1500	1200	1500	1200	1500
4	234	1303	1700	3	1500x1900		2000	2100	1300	1700	1300	1700	1300	1700
5	607	1237	1450	5	1600x1300		2000	2550	1500	1900	1500	1900	1500	1900
6	555	1224	1500	5	1600x1300		2550	1500	1500	1200	1500	1200	1500	1200
7	607	1450	1237	5	1600x1300		2550	2100	1600	1300	1600	1300	1600	1300
8	607	1237	1450	5	1600x1300
9	607	1237	1950	#N/A	No can do!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Thanks very much Stephen I'll try this tomorrow! This is the sort of thing I was thinking of, using arrays to make it shorter but I couldn't think how to do it. I'll let you know the results

 

[excelnoob123456]

... where

Headers: G1:N1
Sizes: G3:N7

Can't quite work out how this is supposed to work, I tried it as you have typed it always n/a's out and I evaluated the formula and the numbers being multiplied means it eventually ends up with something like match(2500000,250000) which of course returns n/a, looking into how to get it to work at the minute but I don't quite get how it's supposed to work at the moment tbh.

 

[StephenCrump]

Can't quite work out how this is supposed to work ...

These are range names.

Here's my workbook: https://app.box.com/s/c1u4yoa96v6idbpjq55l85ii0b9z027r

Select the range G1:N1, for example, and you'll see I've given it the range name Headers.

It's possible you're also not entering the formulae as arrays, i.e. CTRL-SHIFT-ENTER rather than just ENTER.

 

[markmzz]

Hi!

Maybe the suggestions below can help you (with StephenCrump's layout)

Lets go:

1) Created the names below in Sheet1:

HTable - Refers to: =Sheet1!$G$1:$N$1

DTable - Refers to: =Sheet1!$G$3:$N$7

2) Created the names below (with cell D3 selected) in Sheet1:

ColSA - Refers to: =MATCH(Sheet1!$A3,HTable,0)

RowRes - Refers to:

=MIN(IF(INDEX(IF(Sheet1!$A3=HTable,IF((Sheet1!$B3<=INDEX(DTable,,ColSA))*
(Sheet1!$C3<=INDEX(DTable,,ColSA+1)),DTable,0),0),,ColSA)>0,ROW(DTable)-ROW(INDEX(DTable,1,1))+1))

3) Finally, put the formula below in cell D2 and copy down:

=IFERROR(INDEX(DTable,RowRes,ColSA)&"x"&INDEX(DTable,RowRes,ColSA+1),"width too large")

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							222	222	234	234	555	555	607	607
2	Type	SizeA	SizeB	Result			SizeA	SizeB	SizeA	SizeB	SizeA	SizeB	SizeA	SizeB
3	222	1436	2050	2000x2100			2000	1500	1200	1500	1200	1500	1200	1500
4	234	1303	1700	1500x1900			2000	2100	1300	1700	1300	1700	1300	1700
5	607	1237	1450	1300x1700			2000	2550	1500	1900	1500	1900	1500	1900
6	555	1224	1500	1300x1700			2550	1500	1500	1200	1500	1200	1500	1200
7	607	1450	1237	1500x1900			2550	2100	1600	1300	1600	1300	1600	1300
8	607	1237	1450	1300x1700
9	607	1237	1950	width too large
***	******	******	******	**************	***	***	******	******	******	******	******	******	******	******

<tbody>
</tbody>

I hope that the suggestions above helps.

Markmzz

 

[markmzz]

Hi!

Another suggestion (more robust)

Lets go:

1) Created the names below in Sheet1:

HTable - Refers to: =Sheet1!$G$1:$N$1

DTable - Refers to: =Sheet1!$G$3:$N$7

Type - Refers to: =Sheet1!$A$3:$A$18

SizeA - Refers to: =Sheet1!$B$3:$B$18

SizeB - Refers to: =Sheet1!$C$3:$C$18

ColSA - Refers to: =MATCH(INDEX(Type,ROW()-ROW(INDEX(Type,1))+1),HTable,0)

DColSA - Refers to: =INDEX(DTable,,ColSA)

DColSB - Refers to: =INDEX(DTable,,ColSA+1)

ColAxColB - Refers to: =IF((INDEX(SizeA,ROW()-ROW(INDEX(SizeA,1))+1)<=DColSA)*(INDEX(SizeB,ROW()-ROW(INDEX(SizeB,1))+1)<=DColSB),DColSA*DColSB)

RowRes - Refers to: =MATCH(MIN(ColAxColB),ColAxColB,0)

2) Finally, put the formula below in cell D2 and copy down:

=IFERROR(INDEX(DColSA,RowRes)&"x"&INDEX(DColSB,RowRes),"width too large")

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							222	222	234	234	555	555	607	607
2	Type	SizeA	SizeB	Result			SizeA	SizeB	SizeA	SizeB	SizeA	SizeB	SizeA	SizeB
3	222	1436	2050	2000x2100			2000	1500	1200	1500	1200	1500	1200	1500
4	234	1303	1700	1500x1900			2000	2100	1300	1700	1300	1700	1300	1700
5	607	1237	1450	1300x1700			2000	2550	1500	1900	1500	1900	1500	1900
6	555	1224	1500	1300x1700			2550	1500	1500	1200	1500	1200	1500	1200
7	607	1450	1237	1600x1300			2550	2100	1600	1300	1600	1300	1600	1300
8	607	1237	1450	1300x1700
9	607	1237	1950	width too large
10	222	1436	2735	width too large
11	234	1303	1984	width too large
12	607	1237	1953	width too large
13	555	1224	2075	width too large
14	222	2550	2100	2550x2100
15	234	1600	1300	1600x1300
16	222	2000	1500	2000x1500
17	234	1200	1900	1500x1900
18	234	1500	1200	1500x1200
***	******	******	******	**************	***	***	******	******	******	******	******	******	******	******

<tbody>
</tbody>

I hope that the new suggestions above helps.

Markmzz