Using lookup to find values (1 or more) based on criteria, greater than, less than, etc.

[prodigy157]

See my example table below.

order #	cost	red height	yellow height	orange height	v1 weight	v2 weight	v3 weight
Order 20	2,160	40	40	40	3323	3927	4800
Order 22	2,406	45	45	50	3702	4375	5347
Order 23	2,395	45	45	50	3685	4355	5322
Order 25	2,709	45	45	47	4168	4925	6020
Order 27	2,923	45	45	50	4497	5315	6496
Order 30	3,212	50	50	50	4942	6060	7138
Order 31	3,376	40	40	45	5194	6370	7502
Order 35	3,672	59	59	59	5649	7200	8160
Order 38	4,044	50	50	55	6419	8253	8987

<tbody>
</tbody>

Two things:

1. I want to look up the order with the highest cost, as long as certain height and weight requirements are met. For example, what's the most expensive order with a yellow height of no more than 45 and a v2 weight no more than 5000. The answer is order 25, but how do i build a formula to show this.

2. going back to the example above, in addition to being able to list the one with the highest cost, I want to be able to lookup ALL orders that meet the criteria, and have those go into a drop down list, or some other way of showing multitple lookup answers in a single cell. In this case, the correct answers are order 20, 22, 23 and 25.

I've searched the internet a lot and played around with VLOOKUP, MIN, MAX, array formulas, etc., but I'm just going in circles, can't figure it out. Thanks in advance for the help!

 

[Trevor_S]

Not sure how you could do the second question with a formula. The first should be possible, however it would be quite complex, and not easy to amend if your search criteria change.

Have you considered using filters - this would allow you to set criteria you want for each column and see which orders remain?

Alternatively use a macro with a form to allow input of criteria. The macro could loop through the records, concatenating the order numbers of matching records into a single variable that could be written into a single cell, if that's what you need?

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J
1	order #	cost	red height	yellow height	orange height	v1 weight	v2 weight	v3 weight		height
2	Order 20	2,160	40	40	40	3323	3927	4800		45
3	Order 22	2,406	45	45	50	3702	4375	5347		v2
4	Order 23	2,395	45	45	50	3685	4355	5322		5000
5	Order 25	2,709	45	45	47	4168	4925	6020		Order 20
6	Order 27	2,923	45	45	50	4497	5315	6496		Order 22
7	Order 30	3,212	50	50	50	4942	6060	7138		Order 23
8	Order 31	3,376	40	40	45	5194	6370	7502		Order 25
9	Order 35	3,672	59	59	59	5649	7200	8160
10	Order 38	4,044	50	50	55	6419	8253	8987
Sheet1

In J5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10<=J$2,IF($G$2:$G$10<=J$4,ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS(J$5:J5))),"")

 

[prodigy157]

	A	B	C	D	E	F	G	H	I	J
1	order #	cost	red height	yellow height	orange height	v1 weight	v2 weight	v3 weight		height
2	Order 20	2,160	40	40	40	3323	3927	4800		45
3	Order 22	2,406	45	45	50	3702	4375	5347		v2
4	Order 23	2,395	45	45	50	3685	4355	5322		5000
5	Order 25	2,709	45	45	47	4168	4925	6020		Order 20
6	Order 27	2,923	45	45	50	4497	5315	6496		Order 22
7	Order 30	3,212	50	50	50	4942	6060	7138		Order 23
8	Order 31	3,376	40	40	45	5194	6370	7502		Order 25
9	Order 35	3,672	59	59	59	5649	7200	8160
10	Order 38	4,044	50	50	55	6419	8253	8987

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

Sheet1​

In J5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF($D$2:$D$10<=J$2,IF($G$2:$G$10<=J$4,ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS(J$5:J5))),"")

This is FANTASTIC, thank you. Now my only remaining question is - how would this formula be altered to allow the choice between red, yellow and orange height, as well as v1, v2 and v3 weight. Your formula has it hard coded for column's D and G, however I'm going to have two separate cells that, in this scenario for example, would say "yellow height" and "v2 weight." How can I have the formula look at these cells to see the selected option, then go search/lookup in the table to see which column to use, based on the header of that column.

Again, thanks so much, I never would have gotten this far.

 

[Aladin Akyurek]

This is FANTASTIC, thank you. Now my only remaining question is - how would this formula be altered to allow the choice between red, yellow and orange height, as well as v1, v2 and v3 weight. Your formula has it hard coded for column's D and G, however I'm going to have two separate cells that, in this scenario for example, would say "yellow height" and "v2 weight." How can I have the formula look at these cells to see the selected option, then go search/lookup in the table to see which column to use, based on the header of that column.

Again, thanks so much, I never would have gotten this far.

Book1
	A	B	C	D	E	F	G	H	I	J
1	order #	cost	red height	yellow height	orange height	v1 weight	v2 weight	v3 weight		yellow height
2	Order 20	2,160	40	40	40	3323	3927	4800		45
3	Order 22	2,406	45	45	50	3702	4375	5347		v2 weight
4	Order 23	2,395	45	45	50	3685	4355	5322		5000
5	Order 25	2,709	45	45	47	4168	4925	6020		Order 20
6	Order 27	2,923	45	45	50	4497	5315	6496		Order 22
7	Order 30	3,212	50	50	50	4942	6060	7138		Order 23
8	Order 31	3,376	40	40	45	5194	6370	7502		Order 25
9	Order 35	3,672	59	59	59	5649	7200	8160
10	Order 38	4,044	50	50	55	6419	8253	8987
Sheet1 (2)

In J5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(INDEX($C$2:$H$10,0,MATCH(J$1,$C$1:$H$1,0))<=J$2,IF(INDEX($C$2:$H$10,0,MATCH(J$3,$C$1:$H$1,0))<=J$4,ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS(J$5:J5))),"")

 

[prodigy157]

Awesome! Thanks!

 

[Aladin Akyurek]

Awesome! Thanks!

You are welcome.