SOLVED

Cell data based on input lists from multiple columns

New Contributor

It's been several years since I've been an excel junky, so I'm hoping the subject even makes sense.

 

I have a pricing sheet I am working on, that uses lists for in-cell dropdown selection. I was considering IFS (AND) nested statement, but it will likely end up extremely complicated and long for what I want to achieve.

Sheet A, I have 3 columns. 1 and 2 are the list drop down, and 3 is the output based on the selection from 1 and 2.

 

Sheet B contains the data for columns 1, 2 and 3. Column 3 in sheet B does not correlate directly to column 1 and 2 (A1 and B1 =/= C1, but could equal C5).

 

If I'm going about this the wrong way, I'm all ears.

4 Replies
best response confirmed by Cliff_P (New Contributor)
Solution

@Cliff_P 

=INDEX($G$3:$G$20,MATCH(1,(A3=$E$3:$E$20)*(B3=$F$3:$F$20),0))

Maybe with INDEX and MATCH. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The formula can be adapted as required.

index match.JPG

 

@Quadruple_Pawn 

I think maybe my brain wasn't able to accurately depict what I meant, so I've attached two images. First depicts the list data on the second sheet. A3 and B5 for example have a cost of $150. A3 and B11 however also have a cost of $150. A2 and B6 would have a cost of $375,  A2 and B5 would be $250.

 

Screenshot 2022-09-09 at 11.05.32 AM.png

Screenshot 2022-09-09 at 11.14.18 AM.png

 

Based on your screenshot and formula in your response, it seems to follow fixed and not variable outputs?

@Cliff_P 

Does variable output mean that e.g. A3 and B5 can have cost of $150 today and $320 next week? Or is the cost for e.g. A3 and B5 always the (variable) value in D2?

 

The suggested formula follows fixed input and output criteria which can be used in an IF or IFS statement. With INDEX and MATCH you can reference very large datasets whereas IF and IFS are restricted to a certain number of nestings.

 

You were considering IFS (AND) nested statement, but it would likely end up extremely complicated and long. What would be the "Something is true1" and "Value if true1" and the "Something is true2" and "Value if true2" and so on conditions in your IFS formula? From your example i don't understand why A3 and B5 for example have a cost of $150 and i wonder what the cost for A4 and B8 would be. Which rule can be applied to return the cost for any combination?

@Quadruple_Pawn 

 

After reading your response a few times, I think I pretty much sorted this out, and your initial solution will work for what I need. I was over-complicating things for the sake of making it easy for my staff to use the sheet. The price of the A3 and B5 will not change on the data side of things.  I will work on making this simple for my sake, and the sake of the people that will be using the sheet. Thanks!