Excel Formula Help

[Neblett99]

Hi Guys,

I am trying to do an Excel Sheet that shows different prices for a Product depending what term of Contract they have taken.

I have the product in cell D and the 24 month contract price in cell E, 36 Month contract price in F and Outright price in cell G.

I then have a separate sheet that has the Contract Length drop down in Cell E3, product in F3, Model in G3 and price in H3.

I would like to know what Formular to use so that when the contract length is selected in cell E3, H3 will automatically pull the correct price depending what product and Model has been selected in Cells F3 & G3.

Kind Regards

Ben

 

[excel_learnerz]

You can use an INDEX/MATCH function to accomplish this task. Here's an example formula that you can use:

=INDEX(Sheet1!$E$2:$G$10,MATCH(F3&"|"&G3,Sheet1!$A$2:$A$10&"|"&Sheet1!$B$2:$B$10,0),MATCH(E3,Sheet1!$E$1:$G$1,0))

Here's how the formula works:

• The INDEX function is used to return the value from the specified range (Sheet1!$E$2:$G$10) based on the row and column numbers.
• The MATCH function is used twice to determine the row and column numbers for the INDEX function.
• In the first MATCH function, we concatenate the values in F3 and G3 with "|" as a delimiter, and search for this concatenated string in the first two columns of the range A2:B10 in Sheet1.
• In the second MATCH function, we search for the value in E3 in the first row of the range E1:G1 in Sheet1.
• The formula returns the value at the intersection of the row and column numbers determined by the MATCH functions.

Note that in the formula, Sheet1 refers to the sheet that contains the price data for the different product models and contract lengths, and you will need to adjust the range references in the formula to match your actual data.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Neblett99]

Hi & welcome to MrExcel.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi,

Thankyou for the welcome and advise.

Unfortunately, I am using a work computer and we cannot add any outside sources to our systems.

I have attached a couple of screenshots that will hopefully help to see what kind of Formula I will need.

Price Sheet.

Price Checker sheet

 

[Neblett99]

Hi & welcome to MrExcel.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff,

Thankyou for your advice.

I have attached a photo of each of my sheets. Would you be able to have a look at them and see if you can create a working formula so the price in Cell H3 will work out the price for the product depending on what contract length is selected in Cell E3.

Price Sheet.
View attachment 90120

Price Checker sheet
View attachment 90121

 

[Fluff]

Thanks for that.
How about

=CHOOSECOLS(FILTER(Price!E3:G13,Price!D3:D13=G3),MATCH(E4,Price!E2:G2,0))

 

[Neblett99]

Thanks for that.
How about

=CHOOSECOLS(FILTER(Price!E3:G13,Price!D3:D13=G3),MATCH(E4,Price!E2:G2,0))

Hi Flutt,

Thankyou so much, that worked!!!

In the Contract Length cell E3, I have a drop down to be able to select, 24 month or a 36 month contract.

How would I change the formula to count these in also?

 

[Fluff]

Oops, there's a typo, Change the E4 to E3

 

[Neblett99]

Oops, there's a typo, Change the E4 to E3

Hi Fluff,

Thankyou sooo much!!

You are an absolute legend.

Kind Regards

Ben

 

[Fluff]

You're welcome & thanks for the feedback.