Vlookup value with criteria

0
votes

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)
1
excelexcel-formulamaxifs
Hi ..Tried to write few formula by using index / match - but could not succeed in getting desired output. Would like to highly appreciate any guidance on this – Ganesh
Guidance: Use INDEX/AGGREGATE not INDEX/MATCH. Suggestion: Show what you've done so far. – user4039065
Hi..I don't have much experience in this related with index/aggregate/match – Ganesh
=AGGREGATE(14,6,$C$1:$C$5/(($B$1:$B$4="GANESH")*($D$1:$D$4="Y")),1)--By using this able to pull maximum value as 2 – Ganesh
But i need help on how to pull ID coulmn value as "XXX2" – Ganesh

1 Answers

0
votes

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))

enter image description here