If between then VLOOKUP

[osf9]

Dear all,

I'm looking for a smart formula to test if a number is between a range of 2 other number and then I need a susequent VLOOKUP to report on the other spreadsheet the value I need.

Just to explain in a better way with an example:

Value Start	Value End	Text
1	5	Good
6	10	Poor
11	15	Awesome

<tbody>
</tbody>

Now, I have another table where I have:

Value	VLookup
2
7
13

<tbody>
</tbody>

I need to include the Vlookup column the Text related to the range in the first table.

Thanks in advance, best regards, Carlo

 

[svendiamond]

And to be honest, how smart is it to tell someone to use a looooong IF-statement, in stead of a very short vlookup, based on the fact that you "dont know" if my vlookup would work.. had you bothered to learn something by testing what i wrote, before you startet handing out advice, you would have saved all 3 of us something... Me some grievens, the osf9 and yourself some time..

He said he wasn't sure how it would work so I tried something else. Like I said I figured the VLOOKUP would work but I didn't know what it would return for a number right in the middle. You explained that it goes to the lesser value. Thanks. What's with the attitude?

 

[Leandrial]

He said he wasn't sure how it would work so I tried something else. Like I said I figured the VLOOKUP would work but I didn't know what it would return for a number right in the middle. You explained that it goes to the lesser value. Thanks. What's with the attitude?

hmmm attitude... well yes, perhaps a bit of that in there... and even thou I still think you trying to correct a non existent problem (he didnt say it didnt work, just that he didnt understood it... had he tried it and said it didnt work, i would have found the column issue right away... again, why the vlookup solution is better, cuz the error-checking is very simpel on that one) with a long if statement was annoying, I shouldnt give you attitude like that... So please accept my appoligy (that one is def not spelled correctly)... i am sorry that I wrote that. No need for me to get annoyed like that when all you where trying was to help...


best regards

Leandrial

 

[osf9]

Of course it works in that way, but I thought there was something "shorter". I was thinking to something like: "=if(between(A1:B2);VLookup....;"")" but there is no "between" function on Excel (as far as I know).

 

[Leandrial]

If you want a shorter formula then

=[COLOR=#333333]vlookup(A1;Ratings;3;TRUE)
[/COLOR]

to do what you need, then I dont know of any...


Best regards

Leandrial

 

[osf9]

If you want a shorter formula then

=[COLOR=#333333]vlookup(A1;Ratings;3;TRUE)
[/COLOR]

to do what you need, then I dont know of any...


Best regards

Leandrial

I nee to apologize with Leadrial: I tested his formula in the wrong way (I gave the name only to the third column and not to the entire lookup range.

A colleague of mine and me just retested it and it works fine.

Thanks everybody for your precious support.

 

[Leandrial]

Glad to hear you got it to work