Forum Discussion
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,B:B,N:N,"xxNOT FOUND",0)
example:
Cat - White
Cat - White
Cat - Black
Dog - Brown
Cat has 2 different values. I would like to somehow be notified i/o the first value being returned. I would like to tag it and then make my decision manually. I have hundreds of records.
thank you
11 Replies
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.
Hello Riny,
Sorry for my tardy reply. I am not completely following but I think if you had a peek at the attached excel it would make more sense what I am after. Let me know if you have any questions. thank you for the help!
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.
