To lookup or not ?

[kezzaman]

]Hello everyone,

In general I can find my way in Excel but I am not an expert so I was wondering if someone could help me with the following:

I have a sheet with a lot of columns with different amounts and prizes.
What I want is when I fill in an amount I get the right prize of that article.
And every article is on one row.

Example:

Row 1 --> Column A = Article 001 --> Column B = Here I type in the amount --> Column C = showing price --> Column D = 50.000 --> Column E = € 5,- --> Column F = 100.000 --> Column G = € 4,- etc.....
Row 2 --> Column A = Article 002 --> .........................................

So when I type in colunm B of Row1 the amount 70.000 I must get the price of Column E (5,--) of article 001
when I type in column B of Row1 the amount 102.000 I must get the price of Column F (4,--) of article 001

What is the best way to flight this issue ?
Thank you for your hulp

With kind regards,
Kezzaman

 

[Special-K99]

"when I type in colunm B of Row1 the amount 70.000 I must get the price of Column E (5,--) of article 001"

Why must you get the price 5 in column E for entering 100 in column B? You havent explained what the reasoning is behind this.
Same goes for entering 102 in B and retrieving 4 (which is not in column F - column F says 100, column G is 4 so which is it column F or G)?
Again need the resoning behind it rather than just saying
"I enter this and I want this"

 

[kezzaman]

"when I type in colunm B of Row1 the amount 70.000 I must get the price of Column E (5,--) of article 001"

Why must you get the price 5 in column E for entering 100 in column B? You havent explained what the reasoning is behind this.
Same goes for entering 102 in B and retrieving 4 (which is not in column F - column F says 100, column G is 4 so which is it column F or G)?
Again need the resoning behind it rather than just saying
"I enter this and I want this"

thanks for your reply,

I don't know what you meand with enering 100 in column B.
In Column B I type the amount what the customer is ordering (example 70.000 pieces).
the price which belongs to 70.000 pieces = € 5,-- (amount between 50.000 and 99.999)

 

[Eric W]

Welcome to the MrExcel forum!

That's not the greatest way to lay out your sheet, but you can do something like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Article 1	70	5	50	5	100	4	150	3.5	200	2
Sheet4

Cell Formulas
Range	Formula
C1	C1	=INDEX(1:1,AGGREGATE(14,6,COLUMN(D1:J1)/(MOD(COLUMN(D1:J1),2)=0)/(D1:J1<=B1),1)+1)

 

[Special-K99]

thanks for your reply,

I don't know what you meand with enering 100 in column B.
In Column B I type the amount what the customer is ordering (example 70.000 pieces).
the price which belongs to 70.000 pieces = € 5,-- (amount between 50.000 and 99.999)

My mistake I meant 70