Forum Discussion
Nested if function
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:
I'd use a two-dimensional lookup table:
The formula in C3 is
=INDEX($F$2:$G$4,MATCH(A3,$E$2:$E$4,0),MATCH(B3,$F$1:$G$1,0))
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)I'd use a two-dimensional lookup table:
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.
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?
