2

I have the following table:

Data example

What I want to do is as follows:

  1. User selects either Minor, Medium or Major to specify which column is used.
  2. User then inputs a number 1-100 and the chart then looks to find where the value follows, and returns the text in the far right column.

So if the user selects Minor and inputs 9 it would return "Weapons" but if they selected Medium and input 9 it would return "Armor and Shields".

I thought of one way to do this would be nested if statements with a unique VLOOKUP in each (example below) but I would prefer a much cleaner way to write this but I can't think of any way.

If (A1="Minor",Vlookup(A2, B2:E11,4),if(A2="Medium",Vlookup(A2, C2:E11,3)ECT...)
  • Are your ranges really entered like "05-09"? Then they are text strings, so I wouldn't expect vlookup to perform on them as intended. – user79865 Aug 14 '15 at 7:16
  • @NormalHuman I found that out unfortunately I tried to change them all to be comma separated but it still did not work. For now I have a really large IF(OR(Cell=#,Cell=#)) Statement. – Aaron Aug 14 '15 at 12:33
2

By default, vlookup assumes the data is sorted, and finds the largest element that is less than or equal to the search key. Therefore, you should fill the table with the lower bound for each range:

+-------+--------+-------+---------+
| Minor | Normal | Major | Item    |
|  1    |  1     |  1    | Arrow   |
|  5    | 11     | 11    | Weapons |
| 10    | 21     | 21    | Potion  |
| 45    | 32     | 26    | Ring    |
+-------+--------+-------+---------+

Let's say the above is your range B2:E6. The first step is to filter the columns, so that only two are left: one of the first three, and the last one. For this I would use filter based on regexmatch:

=filter(B2:E6, regexmatch(B2:E2, "^("&A1&"|Item)$"))

For example, if A1 has "Normal" (my favorite mode) then the regular expression is "^(Normal|Item)$" which matches either of two words, and nothing else. So, if we don't do anything else, the result is

+--------+---------+
| Normal | Item    |
|  1     | Arrow   |
| 11     | Weapons |
| 21     | Potion  |
| 32     | Ring    |
+--------+---------+

But of course we don't stop here: the filtered columns should be fed into vlookup:

=vlookup(A2, filter(B2:E6, regexmatch(B2:E2, "^("&A1&"|Item)$")), 2)

And that is the formula you want. For example, if A2 is 15, then the largest entry that is <=A2 is 11, so the returned value is "Weapons".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.