Note : In the above formula, F2 is the lookup value you want to return the whole row based on, A1:D12 is the data range you want to use, A1 indicates the first column number within your data range.
And then drag the fill handle down across the cells to get all matching rows as below screenshot shown:. Sometimes, you may want to look up values in multiple tables, if any of the tables contains the given lookup value as below screenshot shown, in this case, you can combine one or more Vlookup functions together with IFERROR function to perform multiple lookup. Please apply the following formula into a blank cell where you want to put the result:. Then, drag the fill handle down to the cells that you want to apply this formula, and all matched values have been returned as below screenshot shown:.
The Vlookup function also can help you to check if values exist based on another list, for example, if you want to look for the names in column C and just return Yes or No if the name is found or not in column A as below screenshot shown.
Then, drag the fill handle down to the cells that you want to fill this formula, and you will get the result as you need, see screenshot:. Note : In the above formula, C2 is the lookup value you want to check; A2:A10 is the list of range from where lookup values will be found; the number 1 is the column number from where you want to fetch value in your range.
If you work with numerical data, sometimes, when extracting the matched values from the table, you may also need to sum the numbers in several columns or rows.
This section will introduce some formulas to finish this job in Excel. Suppose, you have a product list with sales for several months as below screenshot shown, now, you need to sum all orders in all months based on the given products. Then, drag the fill handle down to copy this formula to other cells you need, and all the values in a row of the first matching value have been summed together, see screenshot:.
The above formula can only sum values in a row for the first matched value. If you want to sum all matches in multiple rows, please use the following formula, and then drag the fill handle down to the cells you want to apply this formula, and you will get the desired result you need, see screenshot:. Note : In the above formula: H2 is the lookup value you are looking for; A2:A9 is the row headers that contain the lookup value; B2:F9 the data range of the numeric values that you want to sum.
Apply the below formula into a blank cell, and then drag the fill handle down to copy this formula to other cells, now, the first matched values based on the specific month in a column have been summed together, see screenshot:.
Note : In the above formula: H2 is the lookup value you are looking for; B1:F1 is the column headers that contain the lookup value; B2:F9 the data range of the numeric values that you want to sum. Maybe the above formulas are difficult for you to remember, in this case, I will recommend a handy feature - Lookup and Sum of Kutools for Excel , with this feature, you can get the result as easy as possible. If you want to sum the values when you need to match both column and row, for example, to get the total value of the product Sweater in month Mar as below screenshot shown.
Please apply the following formula into a cell, and then press Enter key to get the result, see screenshot:. Note : In the above formula: B2:F9 is the data range of the numeric values that you want to sum; B1:F1 is the column headers contain the lookup value that you want to sum based on; I2 is the lookup value within the column headers you are looking for; A2:A9 is the row headers contain the lookup value that you want to sum based on; H2 is the lookup value within the row headers you are looking for.
In your daily work, when analyzing the data, you may need to gather all the necessary information into a single table based on one or more key columns.
To solve this job, the Vlookup function also can do you a favor. For example, you have two tables, the first table contains the products and names data, and the second table contains the products and orders, now, you want to combine these two tables by matching the common product column into one table.
To merge the two tables into one based on a key column, please apply the following formula into a blank cell where you want to get the result, and then drag the fill handle down to the cells that you want to apply this formula, you will get a merged table with the order column joining to the first table data based on the key column data.
Note : In the above formula, A2 is the value you are looking for, E2:F8 is the table to search, the number 2 is the column number in the table from which to retrieve the value. If your common data in the right side and the returned data in the left column within the second table, to merge the order column, the Vlookup function is unable to do the job.
Please copy or enter the below formula into a blank cell, then copy the formula down the column, and the order column has been joined to the first table, see screenshot:. Note : In the above formula, A2 is the lookup value you are looking for, E2:E8 is range of data that you want to return, F2:F8 is lookup range which contains the lookup value.
Note : In the above formula, what the cell references represent as below screenshot shown:. Then, select the first formula cell, and drag the fill handle to copy this formula to other cells as you need:.
Have you ever tried to Vlookup values across multiple worksheet? Supposing I have the following three worksheets with range of data, and now, I want to get part of the corresponding values based on the criteria from these three worksheets to get the result as below screenshot shown. In this case, the Vlookup Values Across Multiple Worksheets tutorial may do you a favor step by step. As we all know, the normal Vlookup function only can help us to return the matched value from another data range, but sometimes, you may want return the corresponding value along with the cell formatting, such as the fill color, font color, font style as below screenshot shown.
This section will talk about how to get the cell formatting with the returned value in Excel. Please do with the following steps to lookup and return its corresponding value along with cell formatting:. In the worksheet contains the data you want to Vlookup, right click the sheet tab and select View Code from the context menu. See screenshots:.
Note : In the above formula, E2 is the value you will look up, A1:C10 is the table range, and number 3 is the column number of the table you want the matched value returned. Then, select the first result cell, and drag the fill handle down to get all results along with their formatting. See screenshot. Normally, when using the Vloook function to look up and return the matched date format value, some number format will be displayed as below screenshot shown.
Obviously, our invoice dates would not necessarily be just on those dates, so we would need to find the latest date largest that is smaller than or equal to our invoice date, which is what the approximate match will do as long as our price list table is sorted in ascending order of date. Also, the lookups can use a whole table of data and allow you to specify which column or row to use, MATCH just works with a simple list: cells in a single column or in a single row.
Usually, in order to make use of MATCH we need to use the value to retrieve the contents of the cell. INDEX takes three arguments: the block of cells that contains our table of values, a row number and an optional column number that define which cell within our block to return the value from.
This takes the value that our MATCH function found and uses it as the row number, with the column number specifying that we should return the value from the second column of our table of data. Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items. Just added to your cart. Continue shopping. Close search. If it didn't, you'd have to enter one of the values in columns C or D to get a result at all.
You enter the same arguments, but it searches in rows instead of columns. This means that the column containing the value you look up should always be located to the left of the column containing the return value. This example shows a small list where the value we want to search on, Chicago, isn't in the leftmost column. It's found in row 4. The formula used is shown in cell A If you want to experiment with lookup functions before you try them out with your own data, here's some sample data.
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0. Using an exact match, searches for the value 0. Because there is no exact match in column A, an error is returned. Using an approximate match, searches for the value 0. Because 0. Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1. Looks up "Axles" in row 1, and returns the value from row 2 that's in the same column column A.
Looks up "Bearings" in row 1, and returns the value from row 3 that's in the same column column B. Looks up "B" in row 1, and returns the value from row 3 that's in the same column.
Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A. Looks up "Bolts" in row 1, and returns the value from row 4 that's in the same column column C. Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same in this case, third column.
There are three rows of values in the array constant, each row separated by a semicolon ;.
0コメント