CODEWITHSUNDEEP
excelexcel-formulavlookup
steveybo
steveybo

Reputation: 61

Excel multiple variable lookups

have a table that i would like it to select the smallest size picture frame that could be used based on the size values, basically return the smallest frame that would fit the image.

For example i have 4 standard sizes:

a       b    c
Size1   150  150
Size2   300  300
Size3   540  570
Size4   800  800

I want to have a size in another cell e.g. 290 x 300 and would like it to pick the smallest size possible to fit i.e. in this case size2.

I've followed a few guides and have the following that will print out the value if the values are exact but not if they are slightly under one of the options

=VLOOKUP($A$8,CHOOSE({1,2},$B$2:$B$5&", "&$A$2:$A$5,$C$2:$C$5),2,0)

Any helo / direction would be much appreactiated!

Thanks

Upvotes: 1

Views: 156

Answers (2)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5195

Assuming order does matter (e.g. there is a difference between 500x550 and 550x500), you can use this array formula:

= INDEX($A$2:$A$5,MATCH(2,MMULT((E2:F2<=$B$2:$C$5)+0,{1;1}),0))

Note this is an array formula, so you must press Ctrl+Shift+Enter after typing this formula rather than just pressing Enter.

See below for working example.

enter image description here

Assuming order does not matter (e.g. there is not a difference between 500x550 and 550x500), the formula gets considerably longer because of reversing the order of the E2:F2 array. There is possibly a better way to do this but this is the easiest way I can think of to do it. Unfortunately Excel has no way of handling 3D arrays, otherwise this would be not much different from the original formula above. Anyway, here is the formula (line breaks added for readability)

= INDEX($A$2:$A$5,MIN(MATCH(2,MMULT((E2:F2<=$B$2:$C$5)+0,{1;1}),0),
  MATCH(2,MMULT((INDEX(E2:F2,N(IF({1},MAX(COLUMN(E2:F2))-
  COLUMN(E2:F2)+1)))<=$B$2:$C$5)+0,{1;1}),0)))

Note this is also an array formula.

See below, working example. Note how it yields the same result as above in every cell except cell G4, since again this is considering 550x500 and 500x550.

enter image description here

Upvotes: 3

James Hawkins
James Hawkins

Reputation: 218

It is not clear what is in cell A8. Going by your question, I assume it must be dimensions in the format "W x H" (example: 290 x 300). If so, try:

In D2: 1

// Copy next down
In D3: D2+1

// Wherever you want it
=CONCATENATE("Size ",MIN(VLOOKUP(LEFT(A8,FIND(" ",A8)-1)+0,B2:D5,3,TRUE),VLOOKUP(RIGHT(A8,LEN(A8)-FIND("x ",A8)-1)+0,C2:D5,2,TRUE)))

Alternatively, if you split the width and height into 2 cells A8 and B8, this simpler version should do the trick:

In D2: 1

// Copy next down
In D3: D2+1

//Wherever you want it
=CONCATENATE("Size ",MIN(VLOOKUP(A8,B2:D5,3,TRUE),VLOOKUP(B8,C2:D5,2,TRUE)))

These assume the sizes all use a "Size #" naming convention. If otherwise, you could add another column at the right to equal column A, then use vlookup to identify the match, like this (again assumes "W x H" in cell A8):

In D2: 1

// Copy next down
In D3: D2+1

// Copy next down
In E2: =A2

// Wherever you want it

=VLOOKUP(MIN(VLOOKUP(LEFT(A8,FIND(" ",A8)-1)+0,B2:D5,3,TRUE),VLOOKUP(RIGHT(A8,LEN(A8)-FIND("x ",A8)-1)+0,C2:D5,2,TRUE)),D2:E5,2,FALSE)

Upvotes: 2

Related Questions