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,020 points
2,597 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,720 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,720 points
519 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,720 points
784 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,720 points
1,111 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,528 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,690 points
3,624 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,690 points
1,893 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,690 points
936 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
3,482 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,720 points
689 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,720 points
730 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