Help automating this IF formula to generate estimated cost

Arinoum

New Member
Joined
Sep 28, 2016
Messages
44
Hi,
I'm trying to create a pricing calculator that automatically calculates the cost of a service depending on which vendor is used and what service line. Tab one has the calculator and tab two has a list of vendors and their services and different rates for these services. See below example:

TAB ONE: C2&3 is a drop down that pulls from tab two a1:G1 (the vendor names). D2&3 pull from tab two a3:a12 (the turn around time) I want to add a formula on F2 to calculate the cost. Something like If C2 = Lori and D2 = 1 business day then return this formula =(7*amount in E2). Any help is greatly appreciated.

SERVICEVENDORTURN AROUND TIMEESTIMATED QTYCOST
T
C

TAB TWO:
1608151946868.png
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hello.

See if is this that you need. :)

Paste this on range"F2" - =IFERROR(INDEX('TAB TWO'!A1:G12,MATCH(D2,'TAB TWO'!A1:A12,0),MATCH(C2,'TAB TWO'!B1:G1,0)+1)*E2,0)

Paste this on range "F3" - =IFERROR(INDEX('TAB TWO'!A1:G12,MATCH(D3,'TAB TWO'!A1:A12,0),MATCH(C3,'TAB TWO'!B1:G1,0)+2)*E3,0)
 
Solution

Arinoum

New Member
Joined
Sep 28, 2016
Messages
44
Hello.

See if is this that you need. :)

Paste this on range"F2" - =IFERROR(INDEX('TAB TWO'!A1:G12,MATCH(D2,'TAB TWO'!A1:A12,0),MATCH(C2,'TAB TWO'!B1:G1,0)+1)*E2,0)

Paste this on range "F3" - =IFERROR(INDEX('TAB TWO'!A1:G12,MATCH(D3,'TAB TWO'!A1:A12,0),MATCH(C3,'TAB TWO'!B1:G1,0)+2)*E3,0)
Thank you so much!!! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,859
Messages
5,627,290
Members
416,236
Latest member
Lynchbox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top