SOLVED

New Contributor

# Excel for Mac Xlookup

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

6 Replies
best response confirmed by CACTUSAF1 (New Contributor)
Solution

# Re: Excel for Mac Xlookup

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

# Re: Excel for Mac Xlookup

Thank you will give it a try

# Re: Excel for Mac Xlookup

@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!

# Re: Excel for Mac Xlookup

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

# Re: Excel for Mac Xlookup

Hi L Z.

thank you for your help.

KR
C

# Re: Excel for Mac Xlookup

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