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 checked against my example to my real data and the examples are good. When I created the fake data I used the real as an example.
I can't use the Linking Account because a LOOKUP would return the first and only first result because for example account 100100 has 4 accounts under it and the key of Linking Account, Account, and Account Name creates a unique key.
I actually found a way to fix the issue. I used second table (H:M) but adding a line after each entry and have that pasted in. In the screenshot below (#2) I just made a new row in the table and manually added Linking account, Linking Account Name, and Linking Account Detailed but this field varies over more than 1500 lines then pasted the Account and Account Name to create the key:
My problem with this is the original source data is over 1500 lines and do so it very manual and columns H:J come from a data source so when there are lines/Linking Accounts added in master data I'd have to rebuild each time the data come in or carefully paste in the correct place as H:J come in a specific order that must be maintained. I have it setup now that when new data comes from master data for H:J I can simply paste in and retain all formats/keys that are XLOOKUPed with no issue (unless there are new lines but I usually verify my data by ensure if there's any new lines and prep my data so it can accommodate those changes effectively). New lines are added every so often but only come maybe 10-20 at a time at a handful times throughout the year.
This could work if I really need to (doing what I did in screenshot #2) but it's not the most ideal.
- so I'm not sure why the alternative/2nd option I sent doesn't work for you. In that option, if the created key can't be found (i.e. that specific account number doesn't exist in the other list, only then fall back and use the linking account)? If there is some other logic you prefer then you need to explain that for us to help. For example if trying to come up with help for you I have been considering and wondering the answer many questions including the following:
a) if you use a 'fall back' you have to also ignore the name so now you are just comparing account number to a 'fall back' account number, is that any more valid than just using the linking account
b) does the 'fall back' have to consider multiple options at the same level. So if 100100300 can't be found then it must use 100100200 or is 100100100 acceptable/preferred? or any account starting with 100100xxx can be used?
c) do account numbers have multiple levels for the same initial sequence. So if there is a 100100100 could there also be a 100100? and if so is that a preferred back up or not?
d) the example you gave has all the accounts in sequence. is that a guarantee? or just for the example. So 100100200 came after 100100100. So can we assume that to be true and use that order to say find the account in the list just before?
e) will the accounts always increment in 100s? so it will also be 100, 200, 300 or might it skip 100,300, 600, ... or have other numbers like 104, 108, 225, ...?
f) is there a fixed limit on the number of levels? In the data example I believe you had 3 levels 100100100 but in a response you went out to 4 levels 100100100100.- I'm not saying yours doesn't work. I was answering other replies and haven't tested yours yet.
