As an example from work, on the left is a two-column data correspondence table
They are warehouse SKUs and commodity SKUs
Some of them correspond one-to-one, but there are very few special cases where there is a one-to-many relationship:
You need to match the SKU information of the product according to the warehouse SKU
If the formula we are using is XLOOKUP lookup
The formula to be entered is:
=XLOOKUP(D2,A:A,B:B)
The results of the one-to-one search match are all out, but if there are multiple match results in the original data, it will only show the first result
How do you match all the results?
Filter+textjoin
First, the Filter formula, the usage used is:
=Filter(Find Column, Find Condition=Find Value)
The difference between it and XLOOKUP is that when there are multiple results, all of them will be found
So when we enter the formula:
=FILTER(B:B,A:A=D3)
It will have all 2 results that match the criteria listed
So, we can combine the transpose formula:
TRANSPOSE，输入的公式是：
=TRANSPOSE(FILTER(B:B,A:A=D2))
When more than one result matches, it will be matched
Because it is an array usage, it will automatically overflow into the cell on the right
IF WE DON'T WANT THE CELL TO OVERFLOW AND LET IT ALL BE DISPLAYED IN COLUMN E RESULTS, WE CAN COMBINE IT WITH THE TEXTJOIN FORMULA
The formula to be entered is:
=TEXTJOIN(";",TRUE,FILTER(B:B,A:A=D2))
When it results in one, it's a one-to-one match
When it results in multiple entries, it is connected with semicolons
This enables a mix of lookup matches
Have you learned this little trick? Try it out!