Combine the VLookup and Match formulas in Microsoft Excel





The VLookup formula returns data from any column you choose in the data table, simply change the number of the column in the third argument.

Although it sounds simple, there is a catch.

How can you determine the number of a column in a data table that contains numerous columns?
How can you easily change the number of the column in the third argument of the VLookup formula?
How can you easily change the column number in multiple formulas in a worksheet from which complex reports are prepared or in a sheet that contains multiple VLookup formulas?

Solution

Insert the Match formula in the third argument of the VLookup formula.

Step 1: Define 2 names, see screen shot

Select Row 1, press Ctrl+F3, type the name Row1 in the Names in workbook field, and click OK.

Select the data table by pressing Ctrl+*. Then press Ctrl+F3, enter Data in the Names in workbook field, and click OK.

Step 2: Enter the Match formula

Open an adjacent worksheet, and select cell A1.

In cell A1 type 4/1/2001.

In cell B1, enter the formula =MATCH (A1, Row1, 0).
(Be careful to enter the value 0 in the third argument to specify the search for an exact value.)

Results of calculation: 7.

Step 3: Enter the Vlookup formula

Enter the account number 201 into cell A2.

Enter the formula =VLOOKUP(A2,Data,B1) in cell B2. In the third argument of the VLookup formula, select a cell which contains the Match formula.

Calculation results: 7,981.

Step 4: Combine the formulas

In the formula bar (Match formula) of cell B1, select the formula without the = sign, press Ctrl+C, and click the Cancel sign (from the left of the formula in the formula bar). Select cell B2, and in the formula bar, select the address B1.
Press Ctrl+V and press Enter.

The final result is a nested formula is
=VLOOKUP (A2, Data, MATCH(A1, Row 1, 0))

Screenshot // Combine the VLookup and Match formulas in Microsoft Excel
Combine the VLookup and Match formulas in Microsoft ExcelCombine the VLookup and Match formulas in Microsoft Excel



15 thoughts on “Combine the VLookup and Match formulas in Microsoft Excel

  1. “I have set up my chart
    I have name my tables
    I figured out the formula for the Fed tax
    but I can’t put together the formula
    for Fed tax combined with Marital Status And dependents”

  2. I am using vlookup to have data in sheet2 from data of sheet1, havinf certain criteria, eventually data in sheet1 having other data to, can i find the the unlinked cell or data in data sheet1

  3. I am using vlookup to have data in sheet2 from data of sheet1, havinf certain criteria, eventually data in sheet1 having other data to, can i find the the unlinked cell or data in data sheet1

  4. Search fot the tip:( at the search box on the left upper side of this screen) Selecting cells that only contain Text in Microsoft Excel

  5. I am trying to formulate the correct syntax for VLOOKUP to find matches of birthdates and last names in the main data table from another source data table that contains some of the same bithdates and last names. I would like to get a true response in an identified column in the main data table beside the birthdates and last names that matches the birthdates and last names from the source data table. I am using Office 2000. I also have access to Office XP. Either one is fine. Whatever is easiest.

  6. I need to determine if what was loaded into a system actually loaded. I am able to get the source spreadsheet and then dump the results into another spreadsheet. Headings are consistent so Part # is one and UPC is another. (there are about 25 more but Im keeping it simple). The other spreadsheet will have the same information unless an error has occurred which is what I am trying to find out. How do I let excel 2000 look at the part number and then go across and compare things like UPC, weight, etc against the other sheet and note where an error has occurred? Any help would be appreciated

  7. I am doing a vlookup to have data from Sheet 1 populate into Sheet 2. The lookup that I am using is all numerical (ie: 7545774), and even though the data is on Sheet 1 it doesn’t populate back into Sheet 2. However on the numbers that do have some letters in them (ie: 754E432), the data populates back into Sheet 2. I have formated the data into text and tried other things but nothing seems to work besides putting a ‘ in front of the data. The problem is I have about 4500 rows to bring over so this is not a solution. Any advice would be appriecated

  8. is there a way to do a vlookup function..and if there is a value match from table 1 and table 2..then to delete that row in able 2?

  9. “I have set up my chart
    I have name my tables
    I figured out the formula for the Fed tax
    but I can’t put together the formula
    for Fed tax combined with Marital Status And dependents”

  10. I am using vlookup to have data in sheet2 from data of sheet1, havinf certain criteria, eventually data in sheet1 having other data to, can i find the the unlinked cell or data in data sheet1

  11. I am trying to formulate the correct syntax for VLOOKUP to find matches of birthdates and last names in the main data table from another source data table that contains some of the same bithdates and last names. I would like to get a true response in an identified column in the main data table beside the birthdates and last names that matches the birthdates and last names from the source data table. I am using Office 2000. I also have access to Office XP. Either one is fine. Whatever is easiest.

  12. I need to determine if what was loaded into a system actually loaded. I am able to get the source spreadsheet and then dump the results into another spreadsheet. Headings are consistent so Part # is one and UPC is another. (there are about 25 more but Im keeping it simple). The other spreadsheet will have the same information unless an error has occurred which is what I am trying to find out. How do I let excel 2000 look at the part number and then go across and compare things like UPC, weight, etc against the other sheet and note where an error has occurred? Any help would be appreciated

  13. I am doing a vlookup to have data from Sheet 1 populate into Sheet 2. The lookup that I am using is all numerical (ie: 7545774), and even though the data is on Sheet 1 it doesn’t populate back into Sheet 2. However on the numbers that do have some letters in them (ie: 754E432), the data populates back into Sheet 2. I have formated the data into text and tried other things but nothing seems to work besides putting a ‘ in front of the data. The problem is I have about 4500 rows to bring over so this is not a solution. Any advice would be appriecated

  14. is there a way to do a vlookup function..and if there is a value match from table 1 and table 2..then to delete that row in able 2?

Leave a Reply

Your email address will not be published. Required fields are marked *


five × 8 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>