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)
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.
- Forgot to mention my logic works perfectly since everything matched to one but now that I’ve increased my sample size I now have multiples hitting one now. This is why I was looking at an OR.
- Thank you for this.
I can’t use the Linking Account because it has a many-to-many relationship.
What I mean in is Linking Account
100100 can be to
100100100
100100200
100100300
Until it changes to say
200100100
And in those 100100XXX accounts can fall under the main Linking Account 100100 but 100100 has AccountName1, 2, 3, and so on which say 100100100 and 100100200 land to AccountName1 but 100100100300 only hits to AccountName2 with the rest hitting to AccountName3.
I created the logic since I was under pressure and may have painted myself into a corner and may need to rethink it.
Definitely will test your logic tomorrow and report back and use your suggestions.
Thank you so much for the help and any continued help.
I’ll play around with my source data and report back tomorrow on it.