SOLVED

Nested if function

Copper Contributor

I have 3 inputs that may be entered in A3 (Fruit, meat, or veg) and 2 inputs (buy or sell) that may be entered in cell B3. I generated a reference table of pricing for the 6 different combinations of inputs. I desire that cell C3 output the result of any of the defined combinations I entered in cells A3 and B3.

This is the formula entered in cell C3:

=IF(A3="FRUIT",IF(B3="BUY",$E$3,$F$3),IF(A3="MEAT",IF(B3="BUY",$G$3,IF(A3="VEG",IF(B3="BUY",$I$3,$J$3),$H$3)))).

Fruit and meat buys and sells work fine, but entering veg in cell A3, returns "False", not the value specified in the formula as can be seen in the screen shot:Screen Shot 2023-02-04 at 10.37.42 AM.png

4 Replies

@jensor4 

With the way you've arranged your worksheet there's no need for a nested IF. You may use an XLOOKUP:

This formula presumes you have carriage returns (alt+enter) in your pricing header.  CHAR(10) accounts for this carriage return.

=XLOOKUP(buysell&CHAR(10)&item,category,prices)

 

 

 

best response confirmed by jensor4 (Copper Contributor)
Solution

@jensor4 

I'd use a two-dimensional lookup table:

S2217.png

The formula in C3 is

 

=INDEX($F$2:$G$4,MATCH(A3,$E$2:$E$4,0),MATCH(B3,$F$1:$G$1,0))

Thanks, good idea.

 

I initially tried to get this done by trying to simplify my challenge and didn't really propose the problem as I should have.

I am trying to create a spreadsheet that will track various inputs and produce the desired outputs. The suggestion you proposed could work but a new table would have to be created for row of input data.

I am attaching a screen shot that will make the requirements clearer. 

Screen Shot 2023-02-09 at 1.11.31 PM copy.jpg

As I enter new data on each row I need a formula in column H to reflect the results shown above. How would you handle that task?

@Hans Vogelaar 

1 best response

Accepted Solutions
best response confirmed by jensor4 (Copper Contributor)
Solution

@jensor4 

I'd use a two-dimensional lookup table:

S2217.png

The formula in C3 is

 

=INDEX($F$2:$G$4,MATCH(A3,$E$2:$E$4,0),MATCH(B3,$F$1:$G$1,0))

View solution in original post