'How do I do this VLOOKUP?

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!

arraysgoogle-sheetsrandomgoogle-sheets-formulavlookup


Solution 1:[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.

Solution 2:[2]

redo your Col1 and leave there only minimal

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

then use:

=VLOOKUP(D1; A:B; 2; 1)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SL8t7
Solution 2 player0

Related Questions