Forum Discussion

CACTUSAF1's avatar
CACTUSAF1
Copper Contributor
Jul 04, 2021
Solved

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

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jul 04, 2021

    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

6 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jul 04, 2021

    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

    • CACTUSAF1's avatar
      CACTUSAF1
      Copper Contributor
      Jul 05, 2021

      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!

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 05, 2021

        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

Resources