CODEWITHSUNDEEP
excelexcel-formulaformulas
Yvette Walker
Yvette Walker

Reputation: 13

How to auto populate data from two different columns based on another cells value in excel

I am hoping someone can help me!

I am trying to work out the formula(s) on how to auto populate data from two different columns based on another cells value in excel.

I have the following headings shown on the sheet that contains all the data:-

System Size Panels | Inverter Type | Sell - FINANCE PRICE | Sell - cash PRICE

I have the following headings where i want to populate the data:-

Payment Type | System Size (kw) | Inverter Type | No. of Panels | RRP $

I have created a dropdown list for the "Payment Type", as follows:-

-cash
-Certegy
-Brighte
-Other

I have also created a dropdown list for the System Size (kw), as follows:-

2.7
3.24
3.78
4.32
4.86
5.4
5.94

SO....I want a formula so that it populates the correct RRP$ based on the Payment Type for the system size (kw) chosen.

So basically we have a cash and finance price list so if the payment type selection from the dropdown list is CASH, then I want the RRP$ cash price to populate and if Certegy, Brighte or Other are chosen then I want the RRP$finance price to populate.

Upvotes: 1

Views: 1854

Answers (2)

Chris Sampson
Chris Sampson

Reputation: 5

I've looked at the screen shots but as they don't show the column letters and row numbers it's difficult to provide an exact syntax, but it should look a bit like the attached screenshot - you'll need to adjust the references to reflect the layout of your worksheets and use absolute ($) references for the data table so that the reference doesn't change if you need to copy the formula down the column. (if the tables are on different sheets the syntax is 'Sheet Name'!D2) Screenshot

Upvotes: 0

Chris Sampson
Chris Sampson

Reputation: 5

If you have only two different price levels (cash and finance) then an if statement combined with a vlookup should do it the syntax would similar to 

=IF(payment = "Cash", vlookup(system size ,data table,cash price),vlookup(system size,datatable,finance price)

If you have more than two or 3 price levels IF statements would be unwieldy and vlookup with index/match would be a better approach.

data dable table where i want to populate data

Upvotes: 1

Related Questions