Returning Lookup Results Based on 3 Criteria

[craigieuk]

Hi,
New to the forum, however what a find! For a self taught beginner your expertise will be of huge value!

I'm working on a project (sample attached), where I need to create a sheet that our employees can enter dimensions in the yellow area, that will lookup the data in the blue area and show all results that fit the criteria. The criteria is that the data in the blue area must be 0.2 to 0.8mm LARGER in all dimensions than those entered in the yellow area.

We're working with approximately 17,500 parts & data so if only I could figure this out I could save our guys a pile of time!

Thanks for anything you can offer.

Craigieuk

[Bob Phillips]

Is this waht you mean?

[rwgrietveld]

This is the best I could do for you.

I've used a filter and a CF. Just play with the WB.

[Bob Phillips]

This is the best I could do for you.

I've used a filter and a CF. Just play with the WB.

Oddly, although you have used the exact same tests in principle as me (you cribbed them :-)), you don't show the second match as a match.

Your test for c15-B4 (29.2-29) evaluates to 1.9999999999..., so it is NOT >= 2 so the test fails.

My method of adding .2 to the fixed value seems to work better with the FP processor than yours of subtracting one from the other. For yours to work, better to use my formula type

=AND($B12>=$B$3+0.2,$B12<=$B$3+0.8,$C12>=$B$4+0.2,$C12<=$B$4+0.8,$D12>=$B$5+0.2,$D12<=$B$5+0.8)

[craigieuk]

Guys,
Thanks so much for that! I can understand now how you achieved it....fantastic and so much appreciated!


Craigieuk

[rwgrietveld]

Bob Phillips,

Beaten by excel itself again! I still think it is hopeless of Excel that (29.2-29) evaluates to 1.9999999999. Your formula is the easy fix for this.

I still like the filter though as now you keep it simple and don't have to use the (very) long INDEX and ROW formula's.

I took a look at your sheet and think that is the way forward. I was just a bit lazy I guess. Your formula's work, also when copying down so perfect.

[Bob Phillips]

Bob Phillips,

Beaten by excel itself again! I still think it is hopeless of Excel that (29.2-29) evaluates to 1.9999999999. Your formula is the easy fix for this.

I still like the filter though as now you keep it simple and don't have to use the (very) long INDEX and ROW formula's.

I took a look at your sheet and think that is the way forward. I was just a bit lazy I guess. Your formula's work, also when copying down so perfect.

You are so right, but it is not Excel really. All computers have FP processors, and most (all?) have 15 digit precision, which is where the small difference arises.

I like the filter too, but I would just add your CF formula to a spare column to return TRUE or FALSE and filter on that column. Filtering can then allow for any number of matches, the table is constrained.