old Excel 2003 help conditional lookup

cookmd

New Member
Joined
Dec 15, 2011
Messages
6
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?
 
Last edited:

cookmd

New Member
Joined
Dec 15, 2011
Messages
6
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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?
 

cookmd

New Member
Joined
Dec 15, 2011
Messages
6
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:87px;" /><col style="width:86px;" /><col style="width:37px;" /><col style="width:39px;" /><col style="width:42px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Item number</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Pricing Level</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Cost</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Price</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Profit</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2.5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">D</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td></tr></table> <br /><br />
Filtered data:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:87px;" /><col style="width:86px;" /><col style="width:37px;" /><col style="width:39px;" /><col style="width:42px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">Item number</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Pricing Level</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Cost</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Price</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Profit</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2.5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1.5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></table> <br /><br />
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

New Member
Joined
Dec 15, 2011
Messages
6
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
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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. :confused:
 

cookmd

New Member
Joined
Dec 15, 2011
Messages
6
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
 

Forum statistics

Threads
1,082,246
Messages
5,363,984
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top