Search code examples
excelexcel-formulaexcel-365

Nested Xlookup [if not found] not functioning as expected, returning results that don't exist with exact match

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)

enter image description here

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) )

Dummy data to use for testing

Code Group
1 Non-billable
1 Non-billable
4 Admin
5 Admin
1 Non-billable
2 Non-billable
2 Non-billable
3 Admin
4 Admin
5 Admin
4 Admin
1 Non-billable
Code Group
1 Billable
2 Billable
3 Non-billable
Solution

  • This should accomplish the desired output, using BYROW() function:

    enter image description here

    =BYROW(G2#,LAMBDA(α, 
 XLOOKUP(CHOOSECOLS(α,1),_Fix[Code],_Fix[Group],
 CHOOSECOLS(α,-1))))

    enter image description here

    =BYROW(G2#,LAMBDA(α, 
     XLOOKUP(CHOOSECOLS(α,1),_Fix[Code],_Fix[Group],
     CHOOSECOLS(α,2))))

    Here is a dummy data to show the usage of CHOOSECOLS() with -ve numbers to get backward columns:

    enter image description here

    • Regular Usages:

    =CHOOSECOLS(B3:E15,G2:J2)

    • To get backward columns:

    =CHOOSECOLS(B3:E15,L2:O2)