Excel for Mac Xlookup

[CACTUSAF1]

Hi All,

I am new to Excel and have been trying to analyse some data to build a Dashboard and i have so far learnt about SumIf and Xlookup but I have hit a road block where all is get as a result is #Value

I am trying to search a random order of six digit with or with an * ( example 15 02 02*) from ordered set of the same Six digit to then give a general formatted answer from anther cell, is this possible?

KR

Charlie

[#2 · Accepted solution]

@CACTUSAF1 

Hi

Not sure this is exactly what you want. If not please upload a picture of what you have and what you expect

in E2

=XLOOKUP(E1,A1:A2,B1:B2, XLOOKUP("*" & E1 & "*",A1:A2,B1:B2,,2),0)

which means: If an exact match of value in E1 can't be found in A1:A2, do another XLOOKUP with wildcards

[#3]

Thank you will give it a try

[#4]

@L z. thank you for getting back to me, please find Book1 attached.

I am trying to check if H2 is in array B2:B2995 then return M1 in E1 cell currently I only get #VALUE!

[#5]

@CACTUSAF1 

Hi

Two things I fixed:

1/ XLOOKUP: the lookup_array and the return_array must be of the same size. You had

lookup_array = B2:B2995 but return array M2:M322

2/ In the formula I suggested earlier, the nested XLOOKUP lookup_value was
"*" & E1 & "*" but yours was "*"&H2 only

See attached file where this is fixed. Formula in H2:

=XLOOKUP(H2,B$2:B$2995,M$2:M$2995,XLOOKUP("*" & H2 & "*",B$2:B$2995,M$2:M$2995,"No match",2),0)

NB: I changed a few entries (highlighted in yellow) in your file to demo. that this works

[#6]

Hi L Z.

thank you for your help.

KR
C

[#7]

You're welcome Charlie. Glad I could help. Thanks for posting back & nice day...