I have this formula that I would like to use a cell variable rather than the hard coded CHOOSE function. When I put the CHOOSE function into a cell I get the error "The range 2022 Tax Schedule::A:H can't use as a single value". Is there a way to do this?
Thanks for your assistance.
VLOOKUP("Interest Income",CHOOSE(1,2022 Tax Schedule::A:H,2023 Tax Schedule::A:H),8,FALSE)
iMac 21.5″, macOS 12.7
I tried that formula (well, one with my own ranges in it) and it worked for me. Mine looked like this
=VLOOKUP("Interest Income",CHOOSE(D2,Sheet 3::'Table 1-1'::A1:B4,Sheet 2::'Table 1-1'::A1:B4),2,FALSE)
where D2 was either 1 or 2
I assume the 1 in the CHOOSE function will be a cell reference, not a hard-coded 1. There is no use for the CHOOSE function if the choice is a hard-coded number.
Badunit, Thanks for your reply. I'm sorry I didn't clearly define my question. I know the CHOOSE function was working in the formula, but I wanted to remove it from the formula and replace it with a cell variable.
I have solved it. Rather than using the CHOOSE function in the cell (A1), which gave me the error, I can use a pop-up where I can also have the two ranges. It still allows me to choose the variable that I want and it works.
VLOOKUP("Interest Income",(A1),8,FALSE)
Thanks again. This community is awesome.
OOPS, a big typo. I forgot the INDIRECT. You got back here before I had a chance to correct it.
VLOOKUP("Interest Income",INDIRECT($A1,TRUE),8,FALSE)
Yes, A1 needs to be text. Actually the CHOOSE function will work if you make it text.
CHOOSE(1,"2022 Tax Schedule::A:H","2023 Tax Schedule::A:H")
The cell references in my formula were actual cell references in colored ovals. When copy-pasted to here they get single quotes wherever Numbers thinks they are needed when converting to plain text. When the plain text formula is copy/pasted from here to Numbers they become cell references again.
I figured that INDIRECT was missing.
I cannot get CHOOSE to convert a string into a cell reference by itself. CHOOSE(1,"B2:C4") gives me the string "B2:C4" and causes an error in the larger formula. It needs INDIRECT around it to convert it to a reference. Maybe that's what you meant?
And this is an interesting use of CHOOSE. I did not know it could return a range. This might be very useful for other things.
Is that the actual formula? That does not work for me. The (A1) needs to be a cell range. How did you create the popup in A1 to have cell ranges that are actual ranges not text?
Yes I agree. I just did a cut/paste from the original formula.
I notice you used single quotes. Is there a difference between using single quotes vs double quotes?
A better formula variable
