Can you vlookup 2 columns

Previous Sheets Stack. Use an array formula. All good points. I need a pipe seperater in there. I have also queried data in corporate databases to extract the data into Excel. I have had no problems with this approach. Kind regards, Dennis.

To avoid the CSE. Neal, Your approach of using Match inside Sumproduct is great — Avoids array entering. Awesome solutions to the problem which I had for weeks.

Thanks a ton. I replaced E and F with B and C and it worked. Thanks, Igor Sample: look for2 result wrongcorrect Thank you. Pingback: Lookup based on 2 values. Unfortunately, the January sales data is listed by staff ID, and you need the data matched to their name and totaled for their monthly sales total to credit the right staff member.

Once you enter the formula, click and drag cell I3 down through cell I7 to add the formulas for your staff. One of the most onerous problems with VLOOKUP is when users make changes columns are added, deleted, or simply moved to a workbook and you must rewrite all the formulas.

This issue is frustrating and time-consuming. When working with Excel, it is important to learn other formulas for performing the same task.

Some functions take up considerably more computing power than others, especially if there are many lines of data in your database. With some newer updates to Microsoft products, more system resources are required than ever before. Features such as excessive or conditional formatting, unused styles, shapes, and certain formulas take up a lot of memory and can cause workbooks to run slowly and even crash.

Lookup functions are notorious for taking up a lot of memory. Another method is to use other formulas that perform the same type of task. The syntax is:. MATCH looks for the value in your specified array and returns its location.

What remains the same in any lookup function is that there needs to be a unique value to look up. Your options to write into this formula are:. To differentiate them and get the department for a specific person, you need unique information for them — in this case, their full name and their employee ID. The lookup array compares the defined employee ID against all of the employee IDs in column C, as well as the defined full name against the other full names in column A. This formula alone returns One consistent theme you will see throughout your Excel training is that there are multiple ways to accomplish the same return using different functions and function combinations.

In the preceding sections, we introduced you to array formulas. These functions, in combination with arrays, provide multiple matches. This file lists a unique row for your sales team and the products they sold in January. Excel runs the IF formula found in the middle first. In other words, the logical test is that the value in D2 Janice Crawford exists in each cell.

What is returned at this point is the virtual positions of the matches. Note: These row numbers do not correspond to the row numbers in the worksheet because it accounts for the two rows used to create the table head. This function analyzes and returns which matches should be returned in a cell. ROW -2 is an incremental counter and places the results of the array in incremental order to not repeat.

The virtual result of this function is 5. Select and drag cell D3 across through cell F3 , and then all three cells D3 to F3 down for three to four cells or how many sold products you think will return to autofill the formula. The result is what three sales team members sold in January. These are separate formulas in each cell, but the results are related to each other and therefore appear in a column-like list.

The first formula Excel runs is the IF formula in the middle. The syntax for IF, as shown above, is:. In other words, the logical test is that the value in D3 Mike Hayes exists in each cell.

If the function returns TRUE , then the row position is returned and two 2 is subtracted from it because of where the row is positioned in the table. This row is positioned two down from the top of the form with the headers in place. The virtual positions of the matches are returned. COLUMN -4 is an incremental counter; it takes the results of your array and puts them in incremental order to not repeat. The virtual result of this function is 1. Select and drag the cell E3 across through cell H3 , and then all four cells E3 to H3 down and across for two cells E5 through H5 to fill in the formula.

This worksheet tab shows the sales team and products sold for the first quarter. The formula you add is similar to that in the exercise for returning multiple values in a column, except you are returning multiple criteria in more than one column. The Month column is added to the lookup table.

Next, click and drag the formula down three or four cells G3, G4, G5, and G6 to add it to the cells beneath G2 :. The result is the data in column form based on two criteria: the staff name and the month of sales. The IF function in this formula is the following:. The -- in the formula is the same as multiplying the result by 1 and can be replaced by 1. This information helps Excel push the results into numeric form. Overall, you are asking for both array conditions to be met.

This worksheet tab has a portion of staff, contact information, department, and ID numbers. Respectively, these three functions each virtually return the following array for Bob Handy in Admin:. The comment will be refreshed after To post as a guest, your comment is unpublished.

Thank you!! Appreciate this page! Super helpful, when other online options weren't. What if you need to have multiple rows as well? Hello, Shelly, Maybe there is not a direct formula for helping you, but, you can apply the below array fromula to get all matching values from the entire row, and then delete or hide the unwanted column as you need.

Please try, hope it can help you! This method does not work. How to vlookup multiple columns in Excel — example Excel Online vlookup to return multiple columns. Excel vlookup array on multiple columns in different workbooks. Excel vlookup compare multiple columns. Share this. Access your data. By continuing to use our site and application, you agree to our Privacy Policy Accept.