Need help with VLOOKUP function

[justaguyfromky]

I am trying to get VLOOKUP to find a number inside of a range of numbers. I
don't want to have to have a separate entry for every number because the
range can be in the thousands. For example if my number is 125, my range of
data is for 1 to 149, but the VLOOKUP will only give a value on the number 1
in this range. Any help would be greatly appreciated.

 

[Peo Sjoblom]

I am not sure I understand 100% what you want but if you mean multiple hits
in a range you can use

=INDEX($D$2:$D$100,SMALL(IF($C$2:$C$100=$H$4,ROW($C$2:$C$100)),ROW(1:1)))

entered with ctrl + shift & enter

then copy down until you get an errror

where D1100 is the range yiou want to retrun a value from and C2:C100 is
the range where you want to match the value which is in H4


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[justaguyfromky]

I have a spreadsheet that has a range of customers, 1-149,150-299,300-499,
and continues... Each customer range represents a different payroll value. If
the customers total is 125, I want a value to populate that represents that
level. I hope this helps to explain what I need excel to do for me. Thank
You

 

[Fred Smith]

As long as your final parameter is True, Vlookup should find what you want.
Specifying True tells Vlookup to find the closest match. Just ensure your
table is sorted in order. The table would look something like:

001 payroll#1
149 payroll#1
150 payroll#2
299 payroll#2
300 payroll#3
499 payroll#3
etc.

The Vlookup would be =vlookup(value,a:b,2,true)

Fred.