Count and Vlookup

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.