» Combine the VLookup and Match formulas in Microsoft Excel
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
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 
Book Store:
Recommended Books:
nesting as shown did not workranjit kumarI could easily do all the steps as shown above. I appreciate the way the entire description was given but i fail to do the last step wherein the two formulas are nested. The nesting is not happening. Please provide screen shots for step NO: 4 more elaboratelyFinding Problem in Multiple ResultsM.Jubbar=VLOOKUP(B5,Sheet1!B15:F18,2,0)
Searching Data with Vlookup function.
and chosing the resulting values one after another.
Like.
For a unique result ===> =VLOOKUP(B5,Sheet1!B15:F18,2,0)
For More then one resulting values...
=VLOOKUP(B5,Sheet1!B15:F18,2,1)
with every change in last fiqure finding the next resulting value...
Help me on this query i Shall be very thank ful....
jubbar007@yahoo.com
Multiple Condition VLOOKUPLanaHello
I am trying to return a value that fulfills a multiple VLOOKUP, i.e. there will be multiple lines for the first condition value of 3996592, and I wish to return a value from the specific line which contains another value of 015. Is there any way to do this?
