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, 2022 in Others by Kithuzzz
• 27,940 points
218 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, 2022 by narikkadan
• 51,600 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,541 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 51,600 points
197 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, 2022 in Others by narikkadan
• 51,600 points
73 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, 2022 in Others by Edureka
• 13,640 points
206 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, 2022 in Others by Edureka
• 13,640 points
374 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, 2022 in Others by Edureka
• 13,640 points
168 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
150 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, 2022 in Others by narikkadan
• 51,600 points
129 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, 2022 in Others by narikkadan
• 51,600 points
62 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