Hey Team,
I inserted a Group column on the transaction tab and getting a weird error. It is weird because the code is still working even though I have the error. Does anything stick out to you all?
=ARRAYFORMULA(IF(ISTEXT(E:E),IFNA(VLOOKUP(E:E,Categories!A:B,2,FALSE),IFERROR(1/0)),IFERROR(1/0)))
The error is “Input must fall within a certain range.”
Thank you!
Brandon
Hi @brandonscottgardner,
That formula works for me to get the Category Group if I put in in the top row of a new Column in my Transactions sheet.
Usually when you get an error, the formula won’t work.
I wonder if you have a Data Validation rule in the cell that generating that error? Click on the formula cell, then Data -> Data validation and see if there is a rule.
Jon
Hey, I want to revist this, @jono. Check out this screenshot. See how it still works but has the error, any ideas?
Is there error you’re referring to the red data validation flags (e.g. in cell D4)? If yes, the issue is that your VLOOKUP is pulling in values that don’t meet the data validation criteria. Just select D2:D and remove the data validation.
Hi @brandonscottgardner,
I’m pretty sure @randy is correct.
There is nothing wrong with your formula. It is working correctly.
You may have copied Column E to get the new Group column D. If you did that, the data validation rule that matches valid Categories in Column E got copied to Column D. You don’t have valid Categories names in column D, since you have Group names. That’s why you have those red errors.
As Randy suggests, remove the Data validation rules in D. Do this by selecting column D, then Data then Data validation. You likely have a rule in Cell range Transactions!D1:D. Use the Remove Validation button to remove.
Let us know if that works.
Jon
BINGO! That worked. Thank you!
