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
• 38,010 points
2,194 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
• 63,700 points

Related Questions In Others

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
• 63,700 points
384 views
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,700 points
572 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,700 points
773 views
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
3,130 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,670 points
1,712 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,670 points
1,558 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,670 points
757 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
• 23,260 points
2,631 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
451 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
• 63,700 points
498 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