Hi all,
I'm trying to create a formula where a value is looked up based on a minimum height and width from an allowed list.
For example there are 5 dimensions allowed: 2 x 4, 5 x 7, 10 x 15, 12 x 20, and 15 x 30.
If i needed to look up 8 x 20, it would need to recognise that although 10 x 15 fits the first criteria, actually 12 x 20 is the smallest to fit both criteria.
I'm thinking it would need to be some sort of a MIN array formula?
Thanks in advance for your help!
Hello sanderb1. Welcome to the forum.
With dimensions allowed in A1:E1 as in the attached and the sizes to lookup in A3:An this array entered formula in B3 and filled down.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Dave
If figures can be split as in the file.
=IFERROR(INDEX($A$2:$A$6&" X "&$B$2:$B$6,AGGREGATE(15,6,ROW($A$2:$A$6)/(($A$2:$A$6>=$D3)*($B$2:$B$6>=$E3)),1)-ROW($A$2)+1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Try this
Please Login or Register to view this content.
Thank you very much, I couldn't make it work without splitting them, but the splitting solution worked perfectly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks