Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Dec 21, 2021

Count and Vlookup

Hello Experts,   How could I modify the below that if there is >1 match on the lookup value (B8) and different results  found in the return array (N:N) then it will somehow notify me?  =XLOOKUP(B8...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 21, 2021

Tony2021 Not sure why you include the lookup value inside the lookup array (i.e. B8 is part of B:B). Usually, you would lookup "Cat" once and return a match from the lookup array. Suppose "Cat" is in cell E3, you could use something like this:

=IF(COUNTIF(B:B,E3)>1,"Multiple",XLOOKUP(E3,B:B,C:C,"Not Found",0))

But since you have XLOOKUP, why not look into the FILTER function to return all values found for "Cat". And use UNIQUE to list all distinct values from column B first.

As a last comment, I'd recommend that you don't use entire columns as lookup- and return arrays. Better to confine them to a range, with a start and an end. The formula could something like this:

=TRANSPOSE(FILTER($C$3:$C$6,$B$3:$B$6=E3))

Even better to use a structured table. Than you can refer to entire columns within that table, which will automatically grow or shrink with the size of the table.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Dec 26, 2021

      Tony2021 Added a formula (in it's roughest form) to the schedule, believing it does what you need.

      =IF(COUNTA(UNIQUE(FILTER(C2:C10,B2:B10=B13)))<>1,"Warning",UNIQUE(FILTER(C2:C10,B2:B10=B13)))

      You could polish it up by using named ranges, rather than direct cell references. To avoid repeating segments, like UNIQUE(FILTER(.......)), within the formula, consider using the LET function.

      • Tony2021's avatar
        Tony2021
        Iron Contributor
        Dec 27, 2021

        Riny_van_Eekelen 

        HI Riny,

        I have a follow up. Thank you for your great solutions provided.
        I tried to simplify the example, however I might have added complexity not knowing how it would affect the unique filter used.

        In my example provided, I am entering values outside of the array. Could this be adjusted to add entries that are part of the array? Essentially eliminating that part down below where you enter outside of the array. In my production file, I would be entering values as part of the array. I hope that makes sense.

         

        I tried to modify the formula with the above in mind and I get a circular reference: 

         

Resources