How to select a preferred value from multiple rows?

[Manojlo]

Hello friends,

There is ID numbers in column A, It can be an individual entry or several entries with the same ID number
In column B, I have just two values WHITE or BLACK
In column D I identified with formulas only unique ID from column A

And now I need help with column E. In this column I want to have WHITE values from column A if it is exist in column A. If WHITE don't exist than BLACK is ok.
When I used VLOOKUP function it returns the first value from the list (from column A), and for example for ID 6666 VLOOKUP returns in column E value BLACK (because BLACK is above WHITE in column A), but I want to see WHITE because it exist.

Thank you in advance.

 

[Joe4]

VLOOKUP always returns the FIRST match it finds.
So one simple way to address your issue is to simply sort your original data, first by ID, and then second by Color, but in DESCENDING order, so the WHITE entries always appear before the BLACK ones.

Alternatively, you could use an IF functions with COUNTIFS to see if there are any white or black entries for that particular ID, by sorting is a quicker/shorter solution.

 

[Manojlo]

VLOOKUP always returns the FIRST match it finds.
So one simple way to address your issue is to simply sort your original data, first by ID, and then second by Color, but in DESCENDING order, so the WHITE entries always appear before the BLACK ones.

Alternatively, you could use an IF functions with COUNTIFS to see if there are any white or black entries for that particular ID, by sorting is a quicker/shorter solution.

Thank you, yes I know that sort (filtering) is one of the solution, but I was looking if there any solution with formula.

 

[Joe4]

Note that Sorting and Filtering are NOT the same thing! They are two very different things!
If you simply sort the data like I suggested, your VLOOKUP formula will work fine.

As long as the values in column D always exist in column A (which sounds like they should, since you seem to be deriving them from your list in column A), then you can use this formula to get what you want (place in cell E2 and copy all the way down to cell E7):

=IF(COUNTIFS(A$2:A$11,D2,B$2:B$11,"WHITE")>0,"WHITE","BLACK")

 

[Manojlo]

Note that Sorting and Filtering are NOT the same thing! They are two very different things!
If you simply sort the data like I suggested, your VLOOKUP formula will work fine.

As long as the values in column D always exist in column A (which sounds like they should, since you seem to be deriving them from your list in column A), then you can use this formula to get what you want (place in cell E2 and copy all the way down to cell E7):

=IF(COUNTIFS(A$2:A$11,D2,B$2:B$11,"WHITE")>0,"WHITE","BLACK")

Thank you very much, this is solution I was looking for.

 

[Joe4]

You are welcome.
Glad I was able to help!