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

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D	E	F
1	Quantity	1	2	3	4	5
2	1000	35%	40%	50%	50%	60%
3	2000	47%	35%	25%	65%	25%
4	3000	10%	25%	12%	15%	11%
5	4000	25%	15%	13%	11%	12%
6	5000	9%	7%	5%	2%	1%
7
8	Quantity	2000
9	Color	3
10	Percent	25%
Sheet9

 

[Walshie1987]

Hi,

Hope the below helps:

=VLOOKUP(A11,A1:F8,MATCH(B11,A1:F1,0),FALSE)

A11 = cell where the qty is input e.g. 1000
A1:F8 = the table for the lookup
B11 = cell where the paint is input e.g. 1 colour
A1:F1 = headers of the columns for the paint e.g. 1 colour 2 colour etc.

If you need any more info just let me know

Cheers
Chris

 

[Dosneau]

Works Great GUys!...thanks...my only concern is...quantities are not exact...i..e in the example i said 1000, 2000, etc...however the user may enter any quantity...so the variable is the following:

0 - 1,000
1,001 - 5,000
5,001 - 10,000
10,001 - 25,000
25,001 - 50,000
50,001 - 100,000
100,001+

Also this is not that important...but colors would be alpha...i.e. 1 Color 2 color, 4Color or Process...etc...but again that is not important because I can always put the word "color" in another cell...just wondering if vlookup/match could do alpha... thanks again!!!

 

[Walshie1987]

Hi,

I've inserted a round function to the nearest 1000:

=VLOOKUP(ROUND(A11,-3),A1:F8,MATCH(B11,A1:F1,0),FALSE)

e.g. if the user inputs 2678 then this number will be rounded to 3000 and the result will be displayed.

Have you more info on the colour problem being alpha? The vlookup should accommodate this I think.

Cheers
Chris

 

[Dosneau]

Hi Chris,
Am I missing something? I get an NA

=VLOOKUP(ROUND(E23,-3),D11:J18,MATCH(E21,D11:J11,0),FALSE)

Thanks,

e23 = Quantity that user enters
d11:d18 = table
e21 = color that user enters
d11:j11 = color titles

Worked fine before, but as soon as i entered round gave me an N/A, works with some numbers...i.e. 1000 - 1499, but 1500 gives an N/A

Also will try vlookup with alpha and let you know.. . Thanks AgAIN!

 

[VoG]

You probably need 3 instead of -3 if your values are formatted as %

 

[Dosneau]

So I changed the cell from percentage to general...and it works for some numbers but not for others...i.e. tried typing in 7000 and gave me n/a....sorry trying to research a little more on rounding?

 

[Walshie1987]

1500 would be rounded up to 2000

Therefore I'd guess you haven't got 2000 in your table?

If you'd like to roundup the numbers to the nearest 5000 as per your last post the following formula will do that:

=VLOOKUP(ROUNDUP(A11/5000,0)*5000,A1:G8,MATCH(C11,A1:G1,0),FALSE)

e.g.

1 rounds up to 5000, 5001 rounds up to 10000.

I'd suggest just sticking with whole numbers as opposed to putting "0 - 5000", "5001 - 1000" etc. in the table. If needed maybe add a column next the cell with a description in it? Makes it easier when writing the formulas.

Cheers
Chris

 

[Walshie1987]

So I changed the cell from percentage to general...and it works for some numbers but not for others...i.e. tried typing in 7000 and gave me n/a....sorry trying to research a little more on rounding?

No prob, again I'd check that the table lists "7000"