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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value Start[/TD]
[TD]Value End[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]Poor[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]15[/TD]
[TD]Awesome[/TD]
[/TR]
</tbody>[/TABLE]

Now, I have another table where I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]VLookup[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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