SOLVED

How to do INDEX(MATCH() or XLOOKUP() with nested OR()?

%3CLINGO-SUB%20id%3D%22lingo-sub-2555993%22%20slang%3D%22en-US%22%3EHow%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296332iB58D4710C86D8F34%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EI%20have%20two%20tables%20and%20need%20assistance%20with%20one%20main%20issue%20but%20I%20think%20I%20may%20have%20discovered%20a%20second%20I%20couldn't%20solve.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMain%20Issue%3A%20I%20look%20at%20the%20table%20on%20the%20left%20(A%3AF)%20in%20column%20C%20and%20match%20that%20in%20table%20on%20the%20right%20(H%3AM)%20for%20column%20H.%20I%20then%20take%20what's%20in%20columns%20B%20and%20C%20for%20that%20specific%20matching%20account%20and%20paste%20in%20the%20matching%20area%20in%20column%20K%20and%20L.%20I%20create%20a%20concatenated%20key%20lookup%20so%20I%20can%20connect%20both%20tables%20(columns%20F%20and%20M)%3A%3C%2FP%3E%3CP%3E%3D%5B%40%5BLinking%20Account%5D%5D%26amp%3B%22%20%22%26amp%3B%5B%40Account%5D%26amp%3B%22%20%22%26amp%3B%5B%40%5BAccount%20Name%5D%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20fix%20the%20N%2FAs.%20How%20do%20I%20make%20a%20lookup%20or%20INDEX(MATCH()%20that%20does%20an%20OR()%20with%20this%20key%20I%20created%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20issue%20I%20noticed%20while%20I%20was%20reviewing%3A%20did%20I%20do%20that%20right%20with%20linking%20the%20tables%3F%20Could%20there%20be%20an%20easier%20way%20to%20link%20them%3F%20I%20have%20to%20create%20the%20concatenation%20with%20the%20three%20cells%20because%20I%20can%20have%20more%20than%20one%20account%20hit%20into%20that%20linking%20account%20in%20the%20second%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2555993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556431%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556431%22%20slang%3D%22en-US%22%3Ei%20see%20that%20you%20are%20using%20XLOOKUP()%20which%20is%20great%20because%20the%20next%20parameter%20for%20XLOOKUP%20is%20%5Bif_not_found%5D%20so%20just%20put%20whatever%20you%20want%20instead%20of%20N%2FA%20there.%20I%20would%20help%20more%20but%20you%20haven't%20said%20what%20that%20%22OR%22%20should%20be%20that%20you%20are%20referring%20to.%20If%20it%20can't%20find%20that%20exact%20account%20combination%20then%20what%20do%20you%20want%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556447%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556447%22%20slang%3D%22en-US%22%3EThank%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20apologize.%20Let%20me%20give%20details.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20NAs%20are%20supposed%20to%20fall%20in%20line%20with%20the%20others%20there.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%3A%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20C%20matches%20column%20H%20for%20C2%20(where%20the%20XLOOKUP%20returns%20correctly)%20and%20also%20for%20C3%20but%20the%20lookup%20returns%20an%20NA.%3CBR%20%2F%3E%3CBR%20%2F%3EWhere%20the%20OR%20would%20be%20needed%20is%20the%20in%20K2%20and%20L2%20as%20well.%3CBR%20%2F%3E%3CBR%20%2F%3EBecause%20the%20first%20two%20rows%20actually%20match%20for%20C2%20and%20C3%20in%20H2%2C%20the%20accounts%20in%20A2%20and%20A2%20differ%20at%20the%20end%20at%20100100XXX%20at%20the%20very%20end%20three.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20OR%20would%20need%20to%20look%20at%20both%3CBR%20%2F%3E100100%20100100100%20Name1%3CBR%20%2F%3E100100%20100100200%20Name2%20and%20return%20DifferentName1%20instead%20of%20the%20NA.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20true%20for%20the%20other%20NAs.%20The%20very%20slight%20difference%20in%20the%20account%20numbers%20(column%20A)%20makes%20it%20tricky%20since%20the%20concatenation%20in%20columns%20D%20and%20M%20can%E2%80%99t%20handle%20both%3CBR%20%2F%3E100100%20100100100%2F100100200%20Name1%2FName2%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20my%20real%20data%20this%20actually%20could%20be%20up%20to%2010%20in%20a%20single%20group.%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%20for%20not%20explaining.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556470%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3Bi'm%20sorry%20but%20maybe%20I'm%20still%20not%20following.%20Why%20not%20just%20use%20the%20%22linking%20Account%22%20in%20the%20XLOOKUP%20instead%20of%20the%20created%20Key%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%5B%40%5BLinking%20Account%5D%5D%2CTable2%5BLinking%20Account%5D%2CTable2%5BLinking%20Account%20Name%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20if%20you%20only%20want%20to%20'fall%20back'%20to%20that%20less%20specific%20match%20condition%20then%20add%20a%20second%20xlookup%20in%20that%20if_not_found%20criteria%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%5B%40%5BCreated%20Key%5D%5D%2CTable2%5BCreated%20Key%5D%2CTable2%5BLinking%20Account%20Name%5D%2CXLOOKUP(%5B%40%5BLinking%20Account%5D%5D%2CTable2%5BLinking%20Account%5D%2CTable2%5BLinking%20Account%20Name%5D))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethe%20problem%20is%20that%20in%20your%20simple%20example%20both%20give%20the%20same%20results%20(the%20first%20I%20put%20in%20col%20E%20and%20the%20latter%20I%20put%20in%20col%20F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556509%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556509%22%20slang%3D%22en-US%22%3EThank%20you%20for%20this.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can%E2%80%99t%20use%20the%20Linking%20Account%20because%20it%20has%20a%20many-to-many%20relationship.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20I%20mean%20in%20is%20Linking%20Account%3CBR%20%2F%3E100100%20can%20be%20to%3CBR%20%2F%3E100100100%3CBR%20%2F%3E100100200%3CBR%20%2F%3E100100300%3CBR%20%2F%3EUntil%20it%20changes%20to%20say%3CBR%20%2F%3E200100100%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20in%20those%20100100XXX%20accounts%20can%20fall%20under%20the%20main%20Linking%20Account%20100100%20but%20100100%20has%20AccountName1%2C%202%2C%203%2C%20and%20so%20on%20which%20say%20100100100%20and%20100100200%20land%20to%20AccountName1%20but%20100100100300%20only%20hits%20to%20AccountName2%20with%20the%20rest%20hitting%20to%20AccountName3.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20created%20the%20logic%20since%20I%20was%20under%20pressure%20and%20may%20have%20painted%20myself%20into%20a%20corner%20and%20may%20need%20to%20rethink%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EDefinitely%20will%20test%20your%20logic%20tomorrow%20and%20report%20back%20and%20use%20your%20suggestions.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much%20for%20the%20help%20and%20any%20continued%20help.%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99ll%20play%20around%20with%20my%20source%20data%20and%20report%20back%20tomorrow%20on%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556512%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20do%20INDEX(MATCH()%20or%20XLOOKUP()%20with%20nested%20OR()%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556512%22%20slang%3D%22en-US%22%3EForgot%20to%20mention%20my%20logic%20works%20perfectly%20since%20everything%20matched%20to%20one%20but%20now%20that%20I%E2%80%99ve%20increased%20my%20sample%20size%20I%20now%20have%20multiples%20hitting%20one%20now.%20This%20is%20why%20I%20was%20looking%20at%20an%20OR.%3C%2FLINGO-BODY%3E
Contributor

1.png

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 match that in table on the right (H:M) for column H. I then take what's in columns B and C for that specific matching account and paste in the matching area in column K and L. I create a concatenated key lookup so I can connect both tables (columns F and M):

=[@[Linking Account]]&" "&[@Account]&" "&[@[Account Name]]

 

I need to fix the N/As. How do I make a lookup or INDEX(MATCH() that does an OR() with this key I created?

 

Second issue I noticed while I was reviewing: did I do that right with linking the tables? Could there be an easier way to link them? I have to create the concatenation with the three cells because I can have more than one account hit into that linking account in the second table.

 

Thank you for any help.

12 Replies
i see that you are using XLOOKUP() which is great because the next parameter for XLOOKUP is [if_not_found] so just put whatever you want instead of N/A there. I would help more but you haven't said what that "OR" should be that you are referring to. If it can't find that exact account combination then what do you want?
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.
best response confirmed by Jpalaci1 (Contributor)
Solution

@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 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.
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.
what is the original dataset before you were halfway to your solution that you're having problems with

@Yea_So @mtarler 

 

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:

2.png

 

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. 

 

@Jpalaci1 

 

I can't suggest a variant to your solution because you started with a solution you are implementing without showing us the sample of the original dataset.  Your solution might get you on the right track or it might not, but without seeing the sample of original dataset and what you're trying to accomplish or to suggest an alternate solution or angle I cannot begin to think of it as you started your question with the solution you are trying to make work.

 

If you could show a sample of the original dataset and explain to us what your desired/expected outcome might allow us to suggest a variant.  From what I can gather from the conversation, a power query + data model with set table data relationships + power pivot path might be a good variant, but then again I do not know what the original dataset looks like and I do not know what your expected results are.

I can’t share the original but I worked through before my last post to see if it works the exact same way and it does.

The example data I have hear follow the same logic to complete what I need done.
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.
Thank you for the help. This worked.