old Excel 2003 help conditional lookup

[cookmd]

I am beating my head against a wall trying to find a solution. Please have mercy.

A B C D E
Item number, Pricing Level, Cost, Price, Profit
1 A 1.00 2.00 1.00
1 B 1.00 2.50 1.50
1 C 1.00 3.00 2.00
2 A 1.50 3.00 1.50
3 D 1.50 3.00 1.50

I want to set excel up so that someone can type in an Item Number and the different pricing levels are displayed.

Essentially: (given Item number X), If X in column A AND Pricing Level Y = C THEN Price = correct cell in D

So that Item Number 1 & Pricing level B = Price 2.50
while Item Number 1 & Pricing level A = Price 2.00

that way a sales rep can type in an item number and the different price levels will auto-populate.

From there I will need to pull in cost and profit amount as well. Essentially this would be a form in Excel 2003.

Any ideas?

 

[cookmd]

I am beating my head against a wall trying to find a solution. Please have mercy.

A, B, C, D, E,
Item number, Pricing Level, Cost, Price, Profit,
1, A, 1.00, 2.00, 1.00,
1, B, 1.00, 2.50, 1.50,
1, C, 1.00, 3.00, 2.00,
2, A, 1.50, 3.00, 1.50,
3, D, 1.50, 3.00, 1.50,

I'm using commas to separate cell values.

I want to set excel up so that someone can type in an Item Number and the different pricing levels are displayed.

Essentially: (given Item number X), If X in column A AND Pricing Level Y = C THEN Price = correct cell in D

So that Item Number 1 & Pricing level B = Price 2.50
while Item Number 1 & Pricing level A = Price 2.00

that way a sales rep can type in an item number and the different price levels will auto-populate.

From there I will need to pull in cost and profit amount as well. Essentially this would be a form in Excel 2003.

Any ideas?

 

[T. Valko]

I am beating my head against a wall trying to find a solution. Please have mercy.

A B C D E
Item number, Pricing Level, Cost, Price, Profit
1 A 1.00 2.00 1.00
1 B 1.00 2.50 1.50
1 C 1.00 3.00 2.00
2 A 1.50 3.00 1.50
3 D 1.50 3.00 1.50

I want to set excel up so that someone can type in an Item Number and the different pricing levels are displayed.

Essentially: (given Item number X), If X in column A AND Pricing Level Y = C THEN Price = correct cell in D

So that Item Number 1 & Pricing level B = Price 2.50
while Item Number 1 & Pricing level A = Price 2.00

that way a sales rep can type in an item number and the different price levels will auto-populate.

From there I will need to pull in cost and profit amount as well. Essentially this would be a form in Excel 2003.

Any ideas?

Does that mean you want a VBA solution or do you want a worksheet formula?

 

[VoG]

Try reorganizing your data like this http://j-walk.com/ss/excel/usertips/tip020.htm

 

[cookmd]

A formula solution would be easiest. I need to retrieve results from two separate worksheets, so If I can get a formula that will work, I should be able to alter it to suit all the sales team's retrieval needs.

when they type in an item number, I need to retrieve price by all price levels, cost, and profit. Thanks for the fast responses!

 

[T. Valko]

A formula solution would be easiest. I need to retrieve results from two separate worksheets, so If I can get a formula that will work, I should be able to alter it to suit all the sales team's retrieval needs.

when they type in an item number, I need to retrieve price by all price levels, cost, and profit. Thanks for the fast responses!

Try this...

Data:

Book1
	A	B	C	D	E
1	Item number	Pricing Level	Cost	Price	Profit
2	1	A	1	2	1
3	1	B	1	2.5	1.5
4	1	C	1	3	2
5	2	A	1.5	3	1.5
6	3	D	1.5	3	1.5
Sheet1

Filtered data:

Book1
	A	B	C	D	E
10	Item number	Pricing Level	Cost	Price	Profit
11	1	A	1	2	1
12	_	B	1	2.5	1.5
13	_	C	1	3	2
14	_	_	_	_	_
Sheet1

This assumes the data is sorted or grouped together by item number.

Enter this formula in B11:

=IF(ROWS(B$11:B11)>COUNTIF($A$2:$A$6,$A$11),"",INDEX(B$2:B$6,MATCH($A$11,$A$2:$A$6,0)+ROWS(B$11:B11)-1))

Copy down until you get blanks then across to column E.

 

[cookmd]

Hi Biff,

Thanks for the response.

I don't think I'm getting this (sorry). In Excel, I have one workbook with two worksheets, sheet1, and sheet2.

I need the formula to reference two different fields on "sheet2" against two separate columns on "sheet1". Once both fields are matched up, then I need the corresponding data to populate from a third column on "sheet1".

WHERE sheet2,C2 is the item number manually entered by a sales rep.

AND

WHERE sheet2,B5 = A
WHERE sheet2,B6 = B
WHERE sheet2,B7 = C
WHERE sheet2,B8 = D


IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B5) = A THEN, Price = (sheet1,D2) ELSE 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B6) = B THEN, Price = (sheet1,D3) ELSE 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B7) = C THEN, Price = 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B8) = D THEN, Price = 'blank'

I only want the reps to see sheet2, type in an item number on sheet2,C2. From there,
sheet2,B5; sheet2,B6; sheet2,B7; sheet2,B8; are auto-populated.

I hope that clears up what I'm needing.
I'm trying to find which formulas to plug in to sheet2, C5-C8, which would then put the appropriate prices to the right of the cells that show price level A B C and D

 

[T. Valko]

Hi Biff,

Thanks for the response.

I don't think I'm getting this (sorry). In Excel, I have one workbook with two worksheets, sheet1, and sheet2.

I need the formula to reference two different fields on "sheet2" against two separate columns on "sheet1". Once both fields are matched up, then I need the corresponding data to populate from a third column on "sheet1".

WHERE sheet2,C2 is the item number manually entered by a sales rep.

AND

WHERE sheet2,B5 = A
WHERE sheet2,B6 = B
WHERE sheet2,B7 = C
WHERE sheet2,B8 = D


IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B5) = A THEN, Price = (sheet1,D2) ELSE 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B6) = B THEN, Price = (sheet1,D3) ELSE 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B7) = C THEN, Price = 'blank'

IF 'Item Number' (sheet2,C2) = 1 (sheet1,A:A) AND 'Pricing Level' (sheet2,B8) = D THEN, Price = 'blank'

I only want the reps to see sheet2, type in an item number on sheet2,C2. From there,
sheet2,B5; sheet2,B6; sheet2,B7; sheet2,B8; are auto-populated.

I hope that clears up what I'm needing.
I'm trying to find which formulas to plug in to sheet2, C5-C8, which would then put the appropriate prices to the right of the cells that show price level A B C and D

Sorry, I don't follow this.

 

[cookmd]

That's Ok. I'm really terrible about explaining things.

I want to cross reference two cells on one worksheet against my data on another worksheet to retrieve the correct price from the second sheet.

In the raw data, Item Numbers are duplicated in the first column, so
the only way to distinguish pricing in the database is by looking up the Item Number in Column A and Pricing Level in column B to get a Price in Column C

Basically,

Item 1, Price Level A is one price.
while
Item 1, Price Level B is a different price.

I have to be able to use both columns (Item Number and Price Level) to find the right price to charge a customer.

To throw another wrench in the works, I need to skip some columns in the formulas.

In my Real Data,

Column A = Item Number
Column C = Price Level
Column G = Price