Forum Discussion
Why is the CHOOSE function not working?
I have a spreadsheet with 3 entry fields:
- B23 | Dropdownlist with 3 options (products)
- C23 | Width
- D23 | Height
In cell E23 I would like to automatically calculate the price based on a matrix price table based on the selection made in the dropdownlist. This is the formula I build:
=IF(OR(D23>B5;C23>N1); "N-A"; INDEX(CHOOSE(B23, "Amazone", Amazone, "Sunset", Sunset, "Fall", Fall), ROW()-1, COLUMN()-1))
"Amazone"/"Sunset"/"Fall" refers to a table with width & height I created in the same sheet.
When I build the formula and click enter, I get the typical error saying
<<A problem has occured with the formule. You don't want to type a formula? If the equal sign (=) or minus sign (-) is being used, Excel considers this to be a formula.
If you type: =1+1, the cell will show 2
You can solve this by typing a (')
If you type: '1+1, the cell will show: =1+1>>
Can someone please tell me how to overcome this issue?
11 Replies
First of all, the CHOOSE function is doing it's best to work; it's virtually impossible that it's Excel that''s at fault here; far more likely is that it's your USE of the CHOOSE function that's not working. Or so I suspect.
But it's hard to diagnose with only your description of what it is that your formula has to work with. In particular, we can't visualize how the "matrix price table" is organized, the source of the data you want to retrieve.
Unless this is something that's highly confidential, would you be willing to post a copy of the spreadsheet itself on OneDrive or GoogleDrive, with a link pasted here that grants access to it. I'm quite confident that a solution will be readily produced
- I uploaded a test file to Dropbox. You can find the link here:
https://www.dropbox.com/scl/fi/l1gnjubum6uvmhb5hpnxt/TableLookup.xlsx?rlkey=1rnnb5p2g8hhrizz9o1xbb231&dl=0
I know I'm not that far from a working solution, but your expertise would be very much appreciated to get this working. Looking forward to it!
If you use comma as decimal separator, use semicolon ; between the arguments of the functions.
If you use point as decimal separator, use comma , between the arguments of the functions.
Don't mix the two.
- Tried to fix it, but no success. Maybe you can give it a try in the XLS file I shared?
Here's a sample file, something I created a while ago using three years worth of US Federal Income Tax tables. When one uses the tax tables to calculate taxes owed, there is first a choice of filing status--Single, Married, Married but Filing as Single, etc.--so just as your spreadsheet needs to choose which table to use, so does this. In fact, there are two choices to be made in order to determine what portion of the single combined table to use and drop downs are used for each:
- Choose the tax year
- Choose the Filing Status
I'm sorry it doesn't come with documentation on how it all works, but it does work and, if I do say so myself, it's pretty smooth. It also makes use of the LET function, which requires a relatively new version of Excel.
