How to get sum of all matches of HLOOKUP in Excel

Question

Let's say I type HLOOKUP("apple", A1:E10, 2, FALSE) instead.

HLOOKUP will then return 100 as it discovers the first match. The apple, however, is already set up in two columns as you can see in the attached image. 100 and 70 are the matching values in row 2. I would like the two values' sum, or 170, to be returned.

In this illustration, columns A and C contain the lookup value. However, it may appear in any number of columns and in any type of column. I want the formula to locate each of the relevant numbers in the second row and add them all up. How do I accomplish that?

Answer 1

Consider:

=SUMPRODUCT((A1:E1="apple")*(A2:E2))

To include more columns than just A through E, use:

=SUMPRODUCT((1:1="apple")*(2:2))