Excel multiple variable lookups

Question

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

Actually listed int he wrong way around above size is on column C I entered 150 150 into a8 for this one — steveybo, Jan 19 '18 at 11:32
Does direction matter? For example, if your reference size is 550x500, this would only fit inside 540x570 if it was rotated to 570x540. In this case, would you want the output to return 540x570, or 800x800? Also, please indicate where your lookup cell(s) are located. Perhaps a screenshot of the spreadsheet would be useful. — ImaginaryHuman072889, Jan 19 '18 at 11:53

ImaginaryHuman072889 · Accepted Answer · 2018-01-19T18:20:04.757

3

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.

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.

edited Jan 19 '18 at 18:20

answered Jan 19 '18 at 17:53

ImaginaryHuman072889

4,953
7
19
51

your first example is perfect! I don't suppose you could clarify for me how it is working? If that's not too much hassel! for example if i wanted to add another variable so it looks between 3 values and gives the best result? Thanks again! Think i've figured it out actually sorry! = INDEX($A$2:$A$5,MATCH(3,MMULT((F2:H2<=$B$2:$D$5)+0,{1;1;1}),0)) Thanks again! – steveybo Jan 25 '18 at 14:21
1

@steveybo More generally, you can use this: `= INDEX($A$2:$A$5,MATCH(COLUMNS(E2:F2),MMULT((E2:F2<=$B$2:$C$5‌)+0,TRANSPOSE((COLUMN(E2:F2)>0‌)+0)),0))`. This will dynamically change `2` to `3` and `{1;1}` to `{1;1;1}`. In my original answer I had this hardcoded because I didn't realize that the number of columns was dynamic. – ImaginaryHuman072889 Jan 25 '18 at 16:18
2

Also, how it works (brief explanation): The part inside `MMULT` is returning a vertical array, where it tells you how many values in "Picture Size" are less than or equal to the "Reference Table". A value of 2 is what you are looking for because this indicates that the first *and* second entry are both less than or equal to it. Then `MATCH(2,)` returns the first index where 2 is found, thus telling you the index of the smallest picture frame where it fits. Then `INDEX` looks up the actual name of the frame (e.g. `Size1`, `Size2`, etc.) – ImaginaryHuman072889 Jan 25 '18 at 16:24
Hi There! thank you so much again, just trying to update this again, im trying to put types into it .ie wooden metal glass etc, how would i go about adding a match case that can work within the mmult command? so if a type is selected it will only give size results for the material selected? – steveybo Mar 07 '18 at 12:45
just trying to think of methods, could i make the first match be case sensitive and then do the sizes based off the type? – steveybo Mar 07 '18 at 12:55

score 2 · Answer 2 · answered Jan 19 '18 at 17:25

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)

Seems like a lot of unnecessary helper columns. Also I'm not a huge fan of `VLOOKUP`. I always prefer `INDEX` and `MATCH` because it is more flexible and has the potential to be way more efficient depending on how large the lookup table is. Still, I think there's enough here for an upvote. Here it comes... — ImaginaryHuman072889, Jan 19 '18 at 18:07
If you are interested, see [this question](https://stackoverflow.com/questions/48348889/excel-vlookup-vs-index-match-which-is-better) for more info about the differences between `INDEX`/`MATCH` and `VLOOKUP`. — ImaginaryHuman072889, Jan 19 '18 at 20:29

Excel multiple variable lookups

2 Answers2