If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
if questions
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 |
#3
|
|||
|
|||
if questions
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 |
#4
|
|||
|
|||
if questions
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 |
#5
|
|||
|
|||
if questions
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 |
Thread Tools | |
Display Modes | |
|
|