Forum Discussion
Solved
How to do INDEX(MATCH() or XLOOKUP() with nested OR()?
I have two tables and need assistance with one main issue but I think I may have discovered a second I couldn't solve. Main Issue: I look at the table on the left (A:F) in column C and ...
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)
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.
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.