Jul 16 2021 06:23 AM - edited Jul 16 2021 06:26 AM
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.
Jul 16 2021 08:05 AM
Jul 16 2021 08:16 AM
Jul 16 2021 08:29 AM
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)
Jul 16 2021 08:37 AM
Jul 16 2021 08:39 AM
Jul 16 2021 04:29 PM
Jul 16 2021 10:56 PM - edited Jul 16 2021 10:59 PM
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.
Jul 16 2021 11:17 PM
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.
Jul 16 2021 11:19 PM
Jul 18 2021 07:55 AM
Jul 18 2021 08:39 AM
Jul 20 2021 11:22 PM
Jul 16 2021 08:29 AM
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)