I have a multi-column spill with data where some of the information is reflected incorrectly. We created a table to search if it’s one of the incorrect records, I would like to search if it exists in the list, and if it does pull the value. This works as expected.

However, if no match is found, I simply want to take the value that exists already in the spill range. Essentially creating another spill range adjacent to it, where I have either the new values we ‘overwrote’ otherwise the original content.

My formula is below the issue is on the part where it says CHOOSECOLS(G2#,2) but is validating as if it matched with the last code:

=XLOOKUP( CHOOSECOLS(G2#,1),_Fix[Code],_Fix[Group], CHOOSECOLS(G2#,2),0)

I solved this but using the formula below, but I’m more curious as to why the [if not found] condition isn’t triggering as expected despite the exact match condition.

=IF( ISERROR( VLOOKUP( CHOOSECOLS(G2#,1),_Fix,2,0) ) , CHOOSECOLS(G2#,2), XLOOKUP( CHOOSECOLS(G2#,1),_Fix[Code],_Fix[Group],0,0) )