Formula to input product pricing based on multiple factors

megush · Jun 14, 2019

Man I hope I explain this correctly...

I use Excel for a purchase order log. Most materials have a set cost & I have a tab in my log with all materials and use VLOOKUP to input the cost of per line item entry based on the item ordered. However, I have 2 products whose prices vary depending on the location of delivery (job site) and would like to know how to write a formula to look for & input job-specific pricing when a) a certain material is selected and b) a certain job name/number is selected but I would need it to also return the standard cost for non job-specific products as it does now. I have NO CLUE how to go about this.

So I need it to look at the material ordered and if its A or B, look for job-specific pricing but if its anything other than A or B, I need it to look for standard pricing like it does now w/ the basic VLOOKUP to my current cost tab.

Thanks in advance for any advice!

kweaver · Jun 14, 2019

Without having sample data, I just make a guess.
Most likely, before you do the VLOOKUP, you'll need an IF statement to see if what you're searching for is one of those two exceptions.

=IF(or(value1=cell, value2 = cell), ...

Then, if that's true, do a VLOOKUP and MATCH combination assuming you might have the associated job-specific info in subsequent columns after the cost of the line items column.
Does that make sense?

kweaver · Jun 14, 2019

I created a possible set-up:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Item[/TD]
[TD="class: xl65, width: 64"]Price[/TD]
[TD="class: xl65, width: 64"]Site1[/TD]
[TD="class: xl65, width: 64"]Site2[/TD]
[TD="class: xl65, width: 64"]Site3[/TD]
[/TR]
[TR]
[TD="class: xl65"]1000[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1001[/TD]
[TD="class: xl65"]14[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1002[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]1003[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1004[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]1005[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]300[/TD]
[/TR]
</tbody>[/TABLE]

Then, I used this combination (1002 and 1005 are the site-dependent items). Change the item number and the site will be associated w/it in the lookup.

Excel 2010

	A	B	C	D
Item	Site3
Price

<tbody>
[TD="align: center"]12[/TD]

[TD="align: right"]1003[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]=IF(OR(B12=1002,B12=1005),"Site?","")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=IF(OR(B12=1002,B12=1005),VLOOKUP(B12,A1:E7,MATCH(D12,A1:E1,0),FALSE),VLOOKUP(B12,A1:B7,2,FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010

A

B

C

D

12

Item

1002

Site?

Site3

13

Price

20

Sheet6

megush · Jun 14, 2019

Sorry...totally forgot to send the sample log link. I use VLOOKUP all the time but never used MATCH and usually only IF for IFERROR. Let me give the above layout & formulas a try...thank you so much!!!!

https://1drv.ms/x/s!AqmnW28CAZhV8DkpIdbHzXckarYA?e=Pw6Am7

kweaver · Jun 14, 2019

Message back if you have an issue. I couldn't tell from your file which items were exceptions to the rule and needed to look further/elsewhere for the pricing.

megush · Jun 17, 2019

So I tried =IF(OR(D10=1. CONCRETE,D10=2. SAND),VLOOKUP(D10,'Materials'!A:Q,MATCH('PO Log'!F10,'Materials'!E1:R1,0)FALSE),VLOOKUP(D10,Materials!A:H,3,FALSE)) & am getting the standard "your formula sucks" error. The materials that are site-specific are 1. Concrete and 2. Sand and the Job Name column would determine the price for each. I moved those prices to the main materials tab rather than having a separate tab for sand and/or concrete pricing. I'm honestly not at all sure what to do w/ that first formula you wrote above. I get what's its going for in saying if material = x or y, go look for Site but I can't seem to make that formula point to another tab & all of my pricing is on a separate tab. My apologies for being obtuse on this & I greatly appreciate your help. Updated sample log included w/ this response.

https://1drv.ms/x/s!AqmnW28CAZhV8DkpIdbHzXckarYA?e=s1NO6K

kweaver · Jun 17, 2019

W/your new workbook, how about this in the K2 (fill down) on PO Log?

Code:

=VLOOKUP(D2,Materials!$A:$Q,MATCH('PO Log'!E2,Materials!$A$1:$R$1,0),FALSE)

megush · Jun 17, 2019

YES! Couple tweaks to my reference tab & I am rolling! Thank you SO MUCH for your help

kweaver · Jun 17, 2019

Any time! Glad to help.

Formula to input product pricing based on multiple factors

megush

New Member

Excel Facts

kweaver

Well-known Member

kweaver

Well-known Member

megush

New Member

kweaver

Well-known Member

megush

New Member

kweaver

Well-known Member

megush

New Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Formula to input product pricing based on multiple factors

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock