Thread: Vlookup value between 2 cells

Hi All,

i need a help to make a vlookup function which looks for a value
between 2 values. the calsse are defined in sheet 1 and the value in
Sheet 2. for examlpe in sheet 2 i want the class to have value C becuse
it's total is between 1001 and 1500.

sorry for the bad languge and i hope it is understode.


Sheet 1
------------

Class min max
A 0 500
B 501 1000
C 1001 1500
D 1501 2000
E 2001 2500

Sheet 2
------------
Total Class
1267 X
2209 X
1109 X
1063 X
1112 X
838 X

hi,

change a little bit your tabel to

Class min max Class
A 0 500 A
B 501 1000 B
C 1001 1500 C
D 1501 2000 D
E 2001 2500 E

and on sheet 2 use the formula
=vlookup(a2,sheet1!$b$2:$d$2,3,1)

note for the sample figures bigger then 2500 the formula will return E
--

hth
regards from Brazil
May the force be with you
Marcelo
*******************


"tayar77@gmail.com" escreveu:

> Hi All,
>
> i need a help to make a vlookup function which looks for a value
> between 2 values. the calsse are defined in sheet 1 and the value in
> Sheet 2. for examlpe in sheet 2 i want the class to have value C becuse
> it's total is between 1001 and 1500.
>
> sorry for the bad languge and i hope it is understode.
>
>
> Sheet 1
> ------------
>
> Class min max
> A 0 500
> B 501 1000
> C 1001 1500
> D 1501 2000
> E 2001 2500
>
> Sheet 2
> ------------
> Total Class
> 1267 X
> 2209 X
> 1109 X
> 1063 X
> 1112 X
> 838 X
>
>

Change your table to this...

Min Class
0 A
501 B
1001 C
1501 D
2001 E


and use the following formula...

=VLOOKUP(A2,Sheet1!$A$2:$B$6,2)

to get a result of this..

Total Class
1267 C
2209 E
1109 C
1063 C
1112 C
838 B


"tayar77@gmail.com" wrote:

> Hi All,
>
> i need a help to make a vlookup function which looks for a value
> between 2 values. the calsse are defined in sheet 1 and the value in
> Sheet 2. for examlpe in sheet 2 i want the class to have value C becuse
> it's total is between 1001 and 1500.
>
> sorry for the bad languge and i hope it is understode.
>
>
> Sheet 1
> ------------
>
> Class min max
> A 0 500
> B 501 1000
> C 1001 1500
> D 1501 2000
> E 2001 2500
>
> Sheet 2
> ------------
> Total Class
> 1267 X
> 2209 X
> 1109 X
> 1063 X
> 1112 X
> 838 X
>
>