Jul 04 2021 10:14 AM
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
Jul 04 2021 10:50 AM
SolutionHi
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
Jul 05 2021 02:07 AM - edited Jul 05 2021 02:14 AM
@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!
Jul 05 2021 02:50 AM
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
Jul 05 2021 03:38 AM
Jul 04 2021 10:50 AM
SolutionHi
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