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

P

prodigy157

New Member
Joined
May 30, 2018
Messages
3
See my example table below.

[TABLE="width: 687"]
<tbody>[TR]
[TD="class: xl68, width: 87"]order #[/TD]
[TD="class: xl68, width: 61"]cost[/TD]
[TD="class: xl68, width: 83"]red height[/TD]
[TD="class: xl68, width: 107"]yellow height[/TD]
[TD="class: xl68, width: 112"]orange height[/TD]
[TD="class: xl68, width: 79"]v1 weight[/TD]
[TD="class: xl68, width: 79"]v2 weight[/TD]
[TD="class: xl68, width: 79"]v3 weight[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 20[/TD]
[TD="class: xl69"] 2,160 [/TD]
[TD="class: xl67, align: center"]40[/TD]
[TD="class: xl67, align: center"]40[/TD]
[TD="class: xl67, align: center"]40[/TD]
[TD="class: xl70, align: center"]3323[/TD]
[TD="class: xl70, align: center"]3927[/TD]
[TD="class: xl70, align: center"]4800[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 22[/TD]
[TD="class: xl69"] 2,406 [/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl70, align: center"]3702[/TD]
[TD="class: xl70, align: center"]4375[/TD]
[TD="class: xl70, align: center"]5347[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 23[/TD]
[TD="class: xl69"] 2,395 [/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl70, align: center"]3685[/TD]
[TD="class: xl70, align: center"]4355[/TD]
[TD="class: xl70, align: center"]5322[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 25[/TD]
[TD="class: xl69"] 2,709 [/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]47[/TD]
[TD="class: xl70, align: center"]4168[/TD]
[TD="class: xl70, align: center"]4925[/TD]
[TD="class: xl70, align: center"]6020[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 27[/TD]
[TD="class: xl69"] 2,923 [/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl70, align: center"]4497[/TD]
[TD="class: xl70, align: center"]5315[/TD]
[TD="class: xl70, align: center"]6496[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 30[/TD]
[TD="class: xl69"] 3,212 [/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl70, align: center"]4942[/TD]
[TD="class: xl70, align: center"]6060[/TD]
[TD="class: xl70, align: center"]7138[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 31[/TD]
[TD="class: xl69"] 3,376 [/TD]
[TD="class: xl67, align: center"]40[/TD]
[TD="class: xl67, align: center"]40[/TD]
[TD="class: xl67, align: center"]45[/TD]
[TD="class: xl70, align: center"]5194[/TD]
[TD="class: xl70, align: center"]6370[/TD]
[TD="class: xl70, align: center"]7502[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 35[/TD]
[TD="class: xl69"] 3,672 [/TD]
[TD="class: xl67, align: center"]59[/TD]
[TD="class: xl67, align: center"]59[/TD]
[TD="class: xl67, align: center"]59[/TD]
[TD="class: xl70, align: center"]5649[/TD]
[TD="class: xl70, align: center"]7200[/TD]
[TD="class: xl70, align: center"]8160[/TD]
[/TR]
[TR]
[TD="class: xl68"]Order 38[/TD]
[TD="class: xl69"] 4,044 [/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl67, align: center"]50[/TD]
[TD="class: xl67, align: center"]55[/TD]
[TD="class: xl70, align: center"]6419[/TD]
[TD="class: xl70, align: center"]8253[/TD]
[TD="class: xl70, align: center"]8987[/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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?
 
Upvote 0

Book1
ABCDEFGHIJ
1order #costred heightyellow heightorange heightv1 weightv2 weightv3 weightheight
2Order 202,16040404033233927480045
3Order 222,406454550370243755347v2
4Order 232,3954545503685435553225000
5Order 252,709454547416849256020Order 20
6Order 272,923454550449753156496Order 22
7Order 303,212505050494260607138Order 23
8Order 313,376404045519463707502Order 25
9Order 353,672595959564972008160
10Order 384,044505055641982538987
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))),"")
 
Upvote 0
Aladin Akyurek said:
ABCDEFGHIJ
order #costred heightyellow heightorange heightv1 weightv2 weightv3 weightheight
Order 20
Order 22v2
Order 23
Order 25Order 20
Order 27Order 22
Order 30Order 23
Order 31Order 25
Order 35
Order 38

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2,160[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]3323[/TD]
[TD="align: center"]3927[/TD]
[TD="align: center"]4800[/TD]
[TD="align: right"][/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2,406[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]3702[/TD]
[TD="align: center"]4375[/TD]
[TD="align: center"]5347[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2,395[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]3685[/TD]
[TD="align: center"]4355[/TD]
[TD="align: center"]5322[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2,709[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]4168[/TD]
[TD="align: center"]4925[/TD]
[TD="align: center"]6020[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2,923[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]4497[/TD]
[TD="align: center"]5315[/TD]
[TD="align: center"]6496[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3,212[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]4942[/TD]
[TD="align: center"]6060[/TD]
[TD="align: center"]7138[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3,376[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]5194[/TD]
[TD="align: center"]6370[/TD]
[TD="align: center"]7502[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3,672[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]5649[/TD]
[TD="align: center"]7200[/TD]
[TD="align: center"]8160[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4,044[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]6419[/TD]
[TD="align: center"]8253[/TD]
[TD="align: center"]8987[/TD]
[TD="align: right"][/TD]

</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))),"")
Click to expand...

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.
 
Upvote 0
prodigy157 said:
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.
Click to expand...


Book1
ABCDEFGHIJ
1order #costred heightyellow heightorange heightv1 weightv2 weightv3 weightyellow height
2Order 202,16040404033233927480045
3Order 222,406454550370243755347v2 weight
4Order 232,3954545503685435553225000
5Order 252,709454547416849256020Order 20
6Order 272,923454550449753156496Order 22
7Order 303,212505050494260607138Order 23
8Order 313,376404045519463707502Order 25
9Order 353,672595959564972008160
10Order 384,044505055641982538987
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))),"")
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top