Vlookup value between 2 cells

[tayar77]

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

 

[Guest]

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


"(e-mail address removed)" escreveu:

 

[Guest]

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