How do I do this VLOOKUP?

Question

trying to figure this out and having no luck.

I have a few cells that contain data displayed as like the following:

+------+------+---+-------+
| Col1 | Col2 | - | Input |
+------+------+---+-------+
| 1    | A    |   |    10 |
| 2-3  | B    |   |       |
| 4-5  | C    |   |       |
| 6-7  | D    |   |       |
| 8+   | E    |   |       |
+------+------+---+-------+

What I am trying to do is a VLOOKUP to return Col2 if a number matches Col1.

So let's say the value in input is 10 (like in the above example), I want it to come back with E because 10 is greater than 8. Likewise, if the input value is 4 or 5, I want it to return C.

Been pulling my hair out over this for a little while, haha, so any help is greatly appreciated!

Answer 1

With a little thanks to @player0, I managed to figure formula out and do it inline without changing my data.

=ARRAYFORMULA(IFERROR(VLOOKUP(RANDBETWEEN(1,10),{VALUE(LEFT(K8:K12,1)),L8:R12},2,1),"Error"))

This will effectively turn the following:

+------+
| Col1 |
+------+
| 1    |
| 2-3  |
| 4-5  |
| 6-7  |
| 8+   |
+------+

Into:

+------+
| Col1 |
+------+
| 1    |
| 2    |
| 4    |
| 6    |
| 8    |
+------+

There is a trick or two for making this work.

• Although it looks like these are numbers, they're text/strings. So we use VALUE to convert them to numbers.
• The other thing to remember is that unless you set the VLOOKUP sorted value to TRUE or 1, you will get an error if your number is higher than 8.