Lookup and then some

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Accidentally posted this in programming, so reposting it in this group:

Greetings,

The following formula (compliments to many fine folks in this forum) is used to copy info from Sheet 1A to Sheet 2:

=IF(ISNA(MATCH(D$3,'1A'!9:9,FALSE)),"",IF($A4="","",VLOOKUP($A4,'1A'!$A:$AH,MATCH(D$3,'1A'!9:9,FALSE),FALSE)))

Autofill is used across and down the worksheet.

$A4 contains the last name of a person. $B4 contains the first name. The problem is sometimes I have more than one person with the same last name, so I actually need the lookup to check both $A4 and $B4 for a match. Is it even possible?

Thanks in advance,

Robbyn
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(D$3,'1A'!9:9,FALSE)),"",IF($A4="","",INDEX(OFFSET('1A'!$
A:$A,0,MATCH(D$3,'1A'!9:9,FALSE)-1),MATCH(1,('1A'!$A:$A=$A4)*('1A'!$B:$
B=$B4),0))))
 
Hi Frank,

Gave your array formula a try and am getting the #NUM error. I'm really hoping I can fix this without having to add another column combining A & B, although that is definitely an option.

BTW, thank you!

Robbyn
 
Back
Top