Hello, i want to compare a cell between two number and return the sample
quantity. I tried to do this using the IF and & functions, but it dosent
works beacuse i put more than 7 if.
The table that i want to use is the following
batch qty sample
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 more than 1250
IN other words, the function has to compare the value that i enter and
compare with the batch qty range and return the sample qty, for example: If
my current batch qty is 1220, the function has to return as sample qty 125
Thanks in advance
Mauro

Have a look in the help index for VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mauro" wrote in message
...
Hello, i want to compare a cell between two number and return the sample
quantity. I tried to do this using the IF and & functions, but it dosent
works beacuse i put more than 7 if.
The table that i want to use is the following
batch qty sample
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 more than 1250
IN other words, the function has to compare the value that i enter and
compare with the batch qty range and return the sample qty, for example:
If
my current batch qty is 1220, the function has to return as sample qty 125
Thanks in advance
Mauro

Try a VLOOKUP ..
Reference table assumed in cols A to C
Assume in E2 down are the various input quantities, eg: 1220
In F2: =IF(E2="","",VLOOKUP(E2,A:C,3))
Copy F2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"Mauro" wrote:
Hello, i want to compare a cell between two number and return the sample
quantity. I tried to do this using the IF and & functions, but it dosent
works beacuse i put more than 7 if.
The table that i want to use is the following
batch qty sample
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 more than 1250
IN other words, the function has to compare the value that i enter and
compare with the batch qty range and return the sample qty, for example: If
my current batch qty is 1220, the function has to return as sample qty 125
Thanks in advance
Mauro

No IFs are needed. We will use VLOOKUP. In A1 thru B14, enter:

2 2
9 3
16 5
26 8
51 13
91 20
151 32
281 50
501 80
1201 125
3201 200
10001 315
35001 500
150001 800

In E1 enter the value to be looked up and in F1 enter:

=IF(E1500000,1250,VLOOKUP(E1,A1:B14,2,TRUE))

--
Gary''s Student - gsnu200805


"Mauro" wrote:

Hello, i want to compare a cell between two number and return the sample
quantity. I tried to do this using the IF and & functions, but it dosent
works beacuse i put more than 7 if.
The table that i want to use is the following
batch qty sample
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 more than 1250
IN other words, the function has to compare the value that i enter and
compare with the batch qty range and return the sample qty, for example: If
my current batch qty is 1220, the function has to return as sample qty 125
Thanks in advance
Mauro

Hi,

If your data is arranged arranged in ascending order as it is, then you can
use the VLOOKUP() formula. However, if that is not the case, then you can
try this:

1. Assume that the data above is in range A5:C10;
2. In E6:E10, enter numbers starting from 1 I.e. 1,2,3,4,5
3. Now in B17, enter your desired number;
4. In C17, enter the following formula
=CHOOSE(SUMPRODUCT(($A$6:$A$10<=$B17)*($B$6:$B$10 =$B17)*($E$6:$E$10)),C$6,C$7,C$8,C$9,C$10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mauro" wrote in message
...
Hello, i want to compare a cell between two number and return the sample
quantity. I tried to do this using the IF and & functions, but it dosent
works beacuse i put more than 7 if.
The table that i want to use is the following
batch qty sample
2 8 2
9 15 3
16 25 5
26 50 8
51 90 13
91 150 20
151 280 32
281 500 50
501 1200 80
1201 3200 125
3201 10000 200
10001 35000 315
35001 150000 500
150001 500000 800
500001 more than 1250
IN other words, the function has to compare the value that i enter and
compare with the batch qty range and return the sample qty, for example:
If
my current batch qty is 1220, the function has to return as sample qty 125
Thanks in advance
Mauro