Thread: VLOOKUP & Array
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default VLOOKUP & Array

Domenic wrote...
Try...

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A3,{"a","b","c"},0)),B1:B3)

Note, however, if "a", "b", or "c" occur more than once in the
range A1:A3, all corresponding values in Column B will be summed.

....

If duplicates could be an issue and the OP wants only the topmost
match for each,

=SUMPRODUCT(ISNUMBER(1/(MATCH({"a","b","c"},A1:A100,0)
=ROW(A1:A100)-MIN(ROW(A1:A100))+1))*B1:B100)