Want to highlight a helpful answer? Upvote!
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
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
