Vlookup value with criteria

Question

I need to find vlookup matching value with predefined criteria from excel.

Criteria:

i) Need to consider picking value only if preferential value is "Y"
ii) In case if name exist in more then two rows, need to consider row with maximum count value.

Example:

Primary Sheet:

ID     Name    Count   Preferrbale

XXX1  Ganesh   1       Y
XXX2  Ganesh   2       Y
XXX3  Ganesh   3       N
XXX4  Bala     4       Y  

Working sheet:

Name    ID ( need to pull as from primary sheet)    
Ganesh  XXX2
Bala    XXX4

--By using this able to pull maximum value as 2

=AGGREGATE(14,6,$C$1:$C$5/(($B$1:$B$4="GANESH")*($D$1:$D$4="Y")),1)

Answer 1

You will have to add worksheet names but this formula should get you started.

=INDEX(A:A, AGGREGATE(15, 7, ROW($2:$5)/((B$2:B$5=G2)*(D$2:D$5="Y")*(C$2:C$5=AGGREGATE(14, 7, C$2:C$5/((B$2:B$5=G2)*(D$2:D$5="Y")), 1))), 1))