# old Excel 2003 help conditional lookup

#### cookmd

##### New Member
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
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
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
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
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
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
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

#### cookmd

##### New Member
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

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

### 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...