Forum Discussion
How to do INDEX(MATCH() or XLOOKUP() with nested OR()?
Jpalaci1 i'm sorry but maybe I'm still not following. Why not just use the "linking Account" in the XLOOKUP instead of the created Key?
=XLOOKUP([@[Linking Account]],Table2[Linking Account],Table2[Linking Account Name])or if you only want to 'fall back' to that less specific match condition then add a second xlookup in that if_not_found criteria like this:
=XLOOKUP([@[Created Key]],Table2[Created Key],Table2[Linking Account Name],XLOOKUP([@[Linking Account]],Table2[Linking Account],Table2[Linking Account Name]))the problem is that in your simple example both give the same results (the first I put in col E and the latter I put in col F)
- Thank you.
I apologize. Let me give details.
The NAs are supposed to fall in line with the others there.
For example:
Column C matches column H for C2 (where the XLOOKUP returns correctly) and also for C3 but the lookup returns an NA.
Where the OR would be needed is the in K2 and L2 as well.
Because the first two rows actually match for C2 and C3 in H2, the accounts in A2 and A2 differ at the end at 100100XXX at the very end three.
The OR would need to look at both
100100 100100100 Name1
100100 100100200 Name2 and return DifferentName1 instead of the NA.
This is true for the other NAs. The very slight difference in the account numbers (column A) makes it tricky since the concatenation in columns D and M can’t handle both
100100 100100100/100100200 Name1/Name2
In my real data this actually could be up to 10 in a single group.
Sorry for not explaining.Jpalaci1 i'm sorry but maybe I'm still not following. Why not just use the "linking Account" in the XLOOKUP instead of the created Key?
=XLOOKUP([@[Linking Account]],Table2[Linking Account],Table2[Linking Account Name])or if you only want to 'fall back' to that less specific match condition then add a second xlookup in that if_not_found criteria like this:
=XLOOKUP([@[Created Key]],Table2[Created Key],Table2[Linking Account Name],XLOOKUP([@[Linking Account]],Table2[Linking Account],Table2[Linking Account Name]))the problem is that in your simple example both give the same results (the first I put in col E and the latter I put in col F)
- Thank you for the help. This worked.
