XLOOKUP gives wrong value

[Bone1897]

Hi,

I am new to the portal. I use Office 365 quite effectively, but I am not able to solve the following problem.

I need to look up for a specific reference in a list of values and return an exact match.
The lookup array is a combination of text values and numerical values (154350 / OVEA350155 / MTKEDA).
The first 4 letters of my lookup value are always the same (OVEA), the rest of the values are different.

Some of the OVEA references have 8 digits and some 6.

The problem arise when I am searching for an OVEA reference with 6 digits and only when there is another OVEA reference with 8 digits out of which the first 6 identical to the digits inside the lookup value. (154350 / OVEA350155 / MTKEDA and 154350 / OVEA35015512 / MTKEDA).

Using XLOOKUP("*"&LookupValue&"*",LookupArray,ReturnArray,"",2) gives wrong result when the first value in the list is the longer one.

Please check the attached file for your reference.

I will appreciate if you can help me out getting the correct result.

Thank you.

[Fluff13]

You just need to add a space before the 2nd * like

Please Login or Register  to view this content.

[ELeGault]

That is true, a space would correct this...

But the thing you need to understand about ALL lookups is its order.

Top to bottom or Left to Right

When it finds the criteria, in this case you are saying stop when you find anything that contains "OVEA450034" and it is finding the amount of 500 related to it because the FIRST one it finds that is that is the top one, which you are saying you dont want.

You can add a space and the top reference no longer meets the criteria "OVEA450034 " that space counts as a value and so it would ignore the reference....

If your data was sorted A-Z in reference it would work without adding a space being that they both match, whichever comes first is the result... the dangers of lookups is this exactly especially when we have data that is so close in likeness!

I dont know what you are doing with the data but there are things to have visible to help you know when there is possible overlap in criteria....

Return the SMALLEST AMOUNT related to the lookup criteria =MINIFS(Table1[Amount],Table1[Reference],"*"&B1&"*")
Return the LARGEST AMOUNT related to the lookup criteria =MAXIFS(Table1[Amount],Table1[Reference],"*"&B1&"*")
Return the NUMBER of records related to the lookup criteria =COUNTIFS(Table1[Reference],"*"&B1&"*")

By seeing the count that have a match it enables you to make the right decision for the criteria, rather then adding a space to your formula... add a space to the end of the Criteria in B1 so that you are intentional about using a space this time vs not the next... If you have the 3 checks above you will see your results dwindle down to 1 match when you add the space where as before the space there are 2...

Hope that helps you understand why this IS working as intended and not as you imagined

[Bone1897]

Hi. I forgot to mention that the / sign is sometimes right after the reference with no space between.

So in my case that might looks like this OVEA350352/MTEDKA and sometimes there is no / at all, so we have plain references.

There is no consistency when it comes to the lookup array values.


Thank you very much.

[ELeGault]

Then I would suggest using what I offered as checks next to your data at all times so that you are aware of the overlap.
I also recommend if you know you want the data with LESS DIGITS to be called upon that you sort your data A-Z but that has to be an operational understanding to the point of knowing that this is what you would "Always" want for results...

If there is a point in which you would say...well this time I want the short reference, but on this next one I want the longer reference...then you need to identify what it is you truly want out of the data

That forward slash is a delimiter on your data and you could reduce the data down to the section you want.

There are more elegant ways to extract that, and I am sure someone here will call it out... but for now this will extract the value that is between the first and second forward slash "/"

Added this to your table and called it NewReference

Please Login or Register  to view this content.

Then you can use the new column for that XLookup

Please Login or Register  to view this content.

Only now we can drop the wild cards because we have stripped it down to the value itsself

Please Login or Register  to view this content.

Again, if there is more than 1 reference with the exact match you will want to sort the data in a way that returns what you want... but this eliminates the spaces and overlap if they are truly unique values where one is an extended version but is its own

Same goes for the check values... they dont need the wild card making it more specific!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Bone1897]

Thank you very much. I will use this technique in future.

[ELeGault]

Happy to help!


____________________________________________________________________________

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[TMS]

Just for fun

Please Login or Register  to view this content.

works for this example.