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

 

[Leandrial]

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

If we say your first range (the one to lookup in) has the name "Ratings" then

=vlookup(A1;Ratings;2;TRUE)

That code should go into B1 in your second range (the one with the values to lookup)


Best regards

 

[osf9]

Sorry, not clear how this formula can understand if value is between start and end.

 

[svendiamond]

Here you go, no need for VLOOKUP.

Leandrial was telling you to use "TRUE" for the last function of the VLOOKUP formula which finds an approximate match. It would work sometimes, but if you had a number right in the middle of two then I don't know which one it would pick.

 

[Leandrial]

Sorry, not clear how this formula can understand if value is between start and end.

What it does is that it looks in your first range, first column for a match to the number you are searching for... Ive statet that it should look for approximate match... That works as follows...

If the search number is say 7, it looks for that number... If it can find 7, it returns from the row... If it cant find 7, it goes through the values, top from buttom, until it find a value that is greater then 7 (in your list 10)... Since its already looking at 10, the row before that has had to be less then 7, since it didnt match... So now the search "nows" that the searchvalue is between the number of the cell it is looking at (10) and greater then the value of the row before (6 in your list)...
And the rule for the search is that if it cant find an exact match, it has to choose the value as close to, but lesser then the search value (6 in your case)... Then it return the value of column 2 in the row that has the value 6 in the first cell...

Does that make sense??

And btw, you didnt tell me if the formula worked for you?? Have you tried it?+

Best regards

Leandrial

 

[Leandrial]

Here you go, no need for VLOOKUP.

Leandrial was telling you to use "TRUE" for the last function of the VLOOKUP formula which finds an approximate match. It would work sometimes, but if you had a number right in the middle of two then I don't know which one it would pick.

It always looks "down" to lesser value



Best regards

Leandrial

 

[Leandrial]

So even the value "9" would be returned with lookup from row with "6" value in it...

So no need not to use VLOOKUP



Best regards

Leandrial

 

[Leandrial]

Here you go, no need for VLOOKUP.

Leandrial was telling you to use "TRUE" for the last function of the VLOOKUP formula which finds an approximate match. It would work sometimes, but if you had a number right in the middle of two then I don't know which one it would pick.

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..

 

[Marcelo Branco]

So even the value "9" would be returned with lookup from row with "6" value in it...

So no need not to use VLOOKUP



Best regards

Leandrial

This is correct, but i think you should use 3 instead of 2 since the searched value is in the 3rd column.

=vlookup(A1;Ratings;2;TRUE)

And maybe OP's Excel version uses , (comma) as argument separator instead of ; (semicolon)

So assuming the data in columns A, B and C, maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Value Start​

[/TD]
[TD]

Value End​

[/TD]
[TD]

Text​

[/TD]
[TD][/TD]
[TD]

Value​

[/TD]
[TD]

VLookup​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD]

Good​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

Good​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

10​

[/TD]
[TD]

Poor​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

Poor​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11​

[/TD]
[TD]

15​

[/TD]
[TD]

Awesome​

[/TD]
[TD][/TD]
[TD]

13​

[/TD]
[TD]

Awesome​

[/TD]
[/TR]
</TBODY>[/TABLE]


Formula in F2 copied down
=VLOOKUP(E2,$A:$C,3,1)

Hope this helps

M.

 

[Leandrial]

This is correct, but i think you should use 3 instead of 2 since the searched value is in the 3rd column.
And maybe OP's Excel version uses , (comma) as argument separator instead of ; (semicolon)

So assuming the data in columns A, B and C, maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Value Start​

[/TD]
[TD]

Value End​

[/TD]
[TD]

Text​

[/TD]
[TD][/TD]
[TD]

Value​

[/TD]
[TD]

VLookup​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD]

Good​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

Good​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

10​

[/TD]
[TD]

Poor​

[/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

Poor​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11​

[/TD]
[TD]

15​

[/TD]
[TD]

Awesome​

[/TD]
[TD][/TD]
[TD]

13​

[/TD]
[TD]

Awesome​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in F2 copied down
=VLOOKUP(E2,$A:$C,3,1)

Hope this helps

M.

OHHHH sorry.... ofc the column would be 3.... A typo on my behalf....

Best regards

Leandrial