How do I do this VLOOKUP?-CodePudding

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!

CodePudding user response：

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)

CodePudding user response：

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.