Forum Discussion
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
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
Lorenzo 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!
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 onlySee 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
- Hi L Z.
thank you for your help.
KR
C
- Thank you will give it a try
