Forum Discussion
Advanced Excel Formula discussion - Problem with dynamic range
Let's assume I want to rent a house. For each house, if the landlord has an agent, I'll contact the agent; If not, I contact the landlord directly. As below: Landlord Agent ...
Instead of CHOOSECOLS(E2:E5,1) it also works with +E2:E5 or T(E2:E5) or E2:E5&"".
XLOOKUP() is expecting a single cell/value and using a range would not work, but using an array does.
Thank you Lewin for the additional options.
I just tested and found both + and &"" returns an array, but T() returns the first value so T(E2:E5) seems no difference from just E2
| Range Reference | Array | Defined Names | |
| T | first | all | first |
Upon further testing, it's interesting to see T() is another function with potential problems. It may recognize first/all values depend on different types of input.