Help index match questions

[Dosneau]

SO I have been trying to figure this out for a while...and ofcourse who better to ask then the good folks at MrExcel. I have a table with the following:

Quantity 1 color 2 color 3 color 4 color 5 color
1000 35% 40% 50% 50% 60%
2000 47% 35% 25% 65% 25%
3000 10% 25% 12% 15% 11%
4000 25% 15% 13% 11% 12%
5000 9% 7% 5% 2% 1%

What I am trying to do is where the user inputs the quantity and the number of colors...and out pops the percentage...but can not figure this one out...tried index and match...and can not figure it out...any help as always is appreciated!!! thankyou

 

[Dosneau]

Instead of Roundup...is there any way a >= can be used?...I have the following criterias: it changes not by a static number i.e. 5000 or even 15000...but random...so basically a user should put any number and it maybe a less than or equal to might work better?

<table border="0" cellpadding="0" cellspacing="0" width="73"><col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:55pt" height="20" width="73"> 1,000 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> 5,000 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> 10,000 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> 25,000 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> 50,000 </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> 100,000 </td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt;border-top:none" height="21"> 100,001 </td> </tr> </tbody></table>

 

[Dosneau]

Changed to "true"
worked perfectly...sorry did not explain correctly!!!

CHRIS!!!thankyoU!!! as well as everyone else!!!..thanks...thanks..


also chris...alpha worked great!...

=VLOOKUP(E23,D11:J18,MATCH(E21,D11:J11,0),TRUE)