Search Function

**Shermaine2010** · 08-31-2011, 03:24 AM

Kindly refer to attachment.

I am understand that vlookup only able to capture most upper column.

My question is how to search out "(ALM)" from the list for selected relationship.

I able get the formula for relationship father & mother cause only have 1 father and 1 mother. Thus vlookup formula is work out.

For sibling case, vlookup formula is error cause have more than 1 sibling.

I would like to capture the sibling name whose have "(ALM)"

Thank you.

**canapone** · 08-31-2011, 03:58 AM

Hi, a possible approach in the file attached using INDEX MATCH in C15

=IF(ISERROR(INDEX($E$4:$E$8,MATCH(A15&B15&"*(ALM)*",INDEX($C$4:$C$8&$D$4:$D$8&$E$4:$E$8,0),))),"",INDEX($E$4:$E$8,MATCH(A15&B15&"*(ALM)*",INDEX($C$4:$C$8&$D$4:$D$8&$E$4:$E$8,0),)))

There are for sure better solutions.

Hope it helps

**Shermaine2010** · 08-31-2011, 05:48 AM

Get it. Thankss

**Shermaine2010** · 08-31-2011, 09:53 PM

New Question is if there have 2 name appear (ALM), i would like to capture most latest date's (ALM) name.

What formula should be apply? Please advice?

**canapone** · 08-31-2011, 10:53 PM

Hi,

if dates of column F are in ascending order, a solution could be (in C15 and below):

=IF(ISERROR(LOOKUP(2;1/(($C$4:$C$8=A15)*($D$4:$D$8=B15)*ISNUMBER(FIND("(ALM)";$E$4:$E$8)));$E$4:$E$8));"";LOOKUP(2;1/(($C$4:$C$8=A15)*($D$4:$D$8=B15)*ISNUMBER(FIND("(ALM)";$E$4:$E$8)));$E$4:$E$8))

Hope it helps

Regards