Excel Add Total Row Full Feature 30

Vlookup and return matched values in multiple columns Normally, applying the Vlookup function can only return the matched value from one column.

In this article, we are going to show you two methods to vlookup and sum the first or all matched values in rows or columns in Excel.

Vlookup and sum matches in a row or multiple rows with formulas Vlookup and sum matches in a column with formulas Easily vlookup and sum matches in rows or columns with an amazing tool More tutorials for VLOOKUP.

Vlookup and sum matches in a row or multiple rows with formulas The formulas in this section can help to sum the first or all matched values in a row or multiple rows based on specific criteria in Excel.

Vlookup and sum the first matched value in a row Supposing you have a fruits table as the below screenshot shown, and you need to lookup the first Apple in the table and then sum all corresponding values in the same row.

To achieve this, please do as follows.

Select a blank cell to output the result, here I select cell B10.

Copy the below formula into it and press the Ctrl Shift Enter keys to get the result.

SUM(VLOOKUP(A10, A2:F7, 2,3,4,5,6, FALSE)) Notes: A10 is the cell containing the value you are looking for; A2:F7 is the data table range (without headers) which include the lookup value and the matched values; The number 2,3,4,5,6 represents that the result value columns start with the second column and end up with the sixth column of the table.

If the number of result columns are more than 6, please change 2,3,4,5,6 to 2,3,4,5,6,7,8,9.

Vlookup and sum all matched values in multiple rows The above formula can only sum values in a row for the first matched value.

If you want to return the sum of all matches in multiple rows, please do as follows.

Select a blank cell (in this case I select cell B10), copy the below formula into it and press the Enter key to get the result.

SUMPRODUCT((A2:A7A10)B2:F7) Easily vlookup and sum matches in rows or columns in Excel: The LOOKUP and Sum utility of Kutools for Excel can help you quickly vlookup and sum matches in rows or columns in Excel as the below demo shown.

Excel Add Total Row Full Feature 30

Download the full feature 30-day free trail of Kutools for Excel now Vlookup and sum matched value in a column with formulas This section provides a formula to return the sum of a column in Excel based on specific criteria.

As the below screenshot shown, you are looking for the column title Jan in the fruit table, and then sum the whole column values.

Please do as follows.

Select a blank cell, copy the below formula into it and press the Enter key to get the result.

SUM(INDEX(B2:F7,0,MATCH(A10,B1:F1,0))) Easily vlookup and sum matches in rows or columns with an amazing tool If you are not good at applying formula, here recommend you the Vlookup and Sum feature of Kutools for Excel.

With this feature, you can easily vlookup and sum matches in rows or columns with only clicks.

Before applying Kutools for Excel, please download and install it firstly.

Vlookup and sum the first or all matched values in a row or multiple rows 1.

Click Kutools Super LOOKUP LOOKUP and Sum to enable the feature.

See screenshot: 2.

In the LOOKUP and Sum dialog box, please configure as follows.

In the Lookup and Sum Type section, select the Lookup and sum matched value(s) in row(s) option; 2.2) In the Lookup Values box, select the cell which contains the value you are looking for; 2.3) In the Output Range box, select a cell to output the result; 2.4) In the Data table range box, select the table range without the column headers; 2.5) In the Options section, if you want to sum values only for the first matched one, choose the Return the sum of the first matched value option.

If you want to sum values for all matches, select the Return the sum of all match values option; 2.6) Click the OK button to get the result immediately.

See screenshot: Note: If you want to vlookup and sum the first or all matched values in a column or multiple columns, please check the Lookup and sum matched value(s) in column(s) option in the dialog box, and then configure as the below screenshot shown.

However, if you need to vlookup value across multiple worksheets, how can you do This article provides detailed steps to help you easily solve the problem.

Excel For Mac Add Total Row