How to get sum of all matches of HLOOKUP in Excel

0 votes

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?

enter image description here

Oct 16 in Others by Kithuzzz
• 20,660 points
80 views

1 answer to this question.

0 votes

Consider:

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

enter image description here

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

=SUMPRODUCT((1:1="apple")*(2:2))
answered Oct 16 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
1,333 views
0 votes
1 answer

How to sum time in Excel?

Assuming your data is located at [D12:D22] Try this ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
98 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
35 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
145 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
248 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
79 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
84 views
0 votes
1 answer

How to look in all folders in directory to change file extensions of excel file?

Loop Through All Subfolders Using VBA Dim strCurrentFileExt ...READ MORE

answered Nov 24 in Others by narikkadan
• 37,660 points
33 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP