Count and Vlookup

Regular Contributor

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.

Screenshot 2021-12-21 at 07.36.16.png

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 

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.

@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: 

Tony2021_0-1640566307249.png

 

@Tony2021 Then I would simply add a column with the warning text. Would that work?

@Tony2021 

 

have you tried using a pivot table?

that worked Riny. I did make a slight modificaiton to return the category if the count is 1.
=IF(COUNTA(UNIQUE(FILTER($N$7:$N$20000,$B$7:$B$20000=B7)))<>1,"Warning - >1 Category for this vendor",UNIQUE(FILTER($N$7:$N$20000,$B$7:$B$20000=B7)))

I thought I could use a named range but in my testing, I received a #value so I guess its not possible with named ranges and that is why I put 20,000 rows down for the range of array.

thanks again for the excellent ideas!
actually I noticed an issue with the named range. It works with named ranges. I thought it was odd that it wouldnt work with named ranges so I checked the range and they were different ranges, which explains the #value. thanks again!

@Tony2021 

 

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.

@Tony2021 

 

Use COUNTIFS() instead of counta()

 

Yea_So_0-1640728850448.png

 

 

 

Looks like you have an extra parenthesis.

@Tony2021 

 

The named range suggestion is for when the referenced workbook is in a closed file state.