Dec 20 2021 04:11 PM - edited Dec 20 2021 04:16 PM
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
Dec 20 2021 10:38 PM
@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.
Dec 25 2021 01:07 PM
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!
Dec 25 2021 08:53 PM
@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.
Dec 26 2021 04:53 PM
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:
Dec 26 2021 09:27 PM
@Tony2021 Then I would simply add a column with the warning text. Would that work?
Dec 28 2021 01:35 PM
Dec 28 2021 01:40 PM
Dec 28 2021 01:51 PM
To troubleshoot your formula, start with the inner most nested formula for example,
you type: =A1:B5, this formula will spill the values of that range
Next what do you want to do with that spilled range value? you want to count it so you wrap the range with a count function:
=COUNT(A1:B5) or maybe you want to use SUMIF or SUMIFS function to get a sum of each category in column then using the result wrap another function around it and so on and so forth.
Dec 28 2021 02:01 PM
Dec 28 2021 02:07 PM