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 | House | Landlord | |
| James | Mary | W | James | |
| Linda | Michael | X | David | |
| Y | Linda | |||
| Z | James |
Column F:
=IFERROR(XLOOKUP(E2:E5,A:A,B:B,E2:E5),E2:E5)
This is correct
Column G:
=XLOOKUP(E2:E5,A:A,B:B,E2:E5)
#VALUE! for X-David
Column H:
=XLOOKUP(E2:E5,A:A,B:B,CHOOSECOLS(E2:E5,1))
Wrong value for X-David, because it returned the first value in range of XLOOKUP([if_not_found]) field
My question is: the only difference between G and H is CHOOSECOLS(). If I put =CHOOSECOLS(E2:E5,1) or =E2:E5 in a separate cell, it gives me same result. However, in XLOOKUP, they are recognized differently. What is the logic behind this?
Thank you for spending time on reading this.
5 Replies
My approaches to returning the landlord as contact where they have no agent were
1. to trap the error
= IFNA(XLOOKUP(landlord, landlords, agent), landlord)2. to look up agent for each house individually using MAP
= MAP(landlord, LAMBDA(name, XLOOKUP(name, landlords, agent, name)) )[I realise the programming style may cause communication difficulties but I stopped using direct cell referencing 10 years ago]
Range Reference Array Defined Names IFERROR Correct Correct Correct XLOOKUP #VALUE! Wrong #VALUE! IFNA #VALUE! #VALUE! Correct MAP Correct Correct Correct Thanks Peter. I just tested and it's interesting to see these different results. The most annoying combination is XLOOKUP+Array, which gives a wrong result, instead of just fail.
I guess one problem of defined names is it does not recognize spilled array like A1# so it's extremely easy to miss new lines.
This may not be of much assistance for your current challenge but there are no issues with using defined names in conjunction with dynamic arrays. A defined name is simply a name given to a snippet of code that, when used within a worksheet formula will execute to formula to which it refers. The most widely used defined names refer to simple range references, e.g.
fixed range = $A$1:$B$40in which case you have an absolute range reference. However, it is quite possible to name the anchor cell of a dynamic range, in which case using the name followed by the # operator is the dynamic range. An alternative is to define the name to refer to the dynamic range by including the # within its definition.
anchor = Sheet1!$A:$1 Worksheet formula = anchor# or dynamicRange =Sheet1!$A$1# Worksheet formula = dynamicRange returns a reference to the same range.Not only that, but
finalRow =TAKE(anchor#,-1)is a reference to the final row of a dynamic range and will move up and down the sheet as the data changes. Please accept my apologies if this is obvious to you, but it is likely that there will be other users that have never had cause to explore the possibilities.
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.
