If we merge the VLOOKUP and MATCH functions together, we can create our own custom formula which will work as a two-way lookup formula that enables us to easily cross check two fields of information in the table.

A two-way lookup formula is useful in a situation where we want to figure out the results for different situations.

To understand Vlookup with Match, lets learn how to vlookup.

**Formula for Vlookup**

To find out how many Cars Steve sold in Alabama City, we will use the Vlookup Function.

**Screenshot 1**

Vlookup looks up a value in a selected range of cells in the left-most column & returns the value in the same row in the index-number position.

**Syntax =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)**

Lets break down all these arguments & understand each one.

**lookup_value:**The value you want to look for**table_array:**The table of data contains information from which you want to returno the output.

o Table array should contain at least two columns of data. The first columncontains the lookup values. These values can be text, numbers, or logical values.**col_index_num:**It contains the data from which you want the information.**range_lookup:**Range lookup is a logical value which instructsVlookup to find an exact or approximate match.The table must be sorted in ascending order. Choices are true or false.True for Approximate match, False for Exact match

**Let’s understand the Vlookup Function step by step as in Screenshot 1**

**C9=VLOOKUP(B9,$B$2:$F$6,2,0)**

- B9 is the lookup value
- $B$2:$F$6 is our range of cells
- 2 is the column index number of the column from which we need to extract the output.
- False or zero for Exact match

If we use **3** as the col_index_num then the formula will return **88** from Column D i.e. itwill provide the **sales** for **David** in **Alabama** City. Refer below screenshot 2

**C9=VLOOKUP(B9,$B$2:$F$6,3,0)**

**Screenshot 2**

Now lets review the match formula excel.

The MATCH function searches for a specified item ina selected range of cells, and then returns the relative position of that item in the range.

**Syntax =MATCH(lookup_value,lookup_array,match_type)**

Let’s break down all these arguments & understand each one.

**lookup_value:**The value you want to look for**lookup_array:**The table of data contains information from which you want to return

the output.**match_type:**1,0 and -1 are three options.1(Default): It will find the largest value in the range. List must be sorted in ascending order.0: It will find an exact match-1: It will find the smallest value in the range. List must be sorted in descending order.

**Example of Match Function**

**Screenshot 3**

**Cell C11=MATCH(C8,$B$2:$F$2,0)**

Let’s understand the Match Function step by step:

- C8 is the lookup value
- $B$2:$F$2 is our range of cells
- 0 for Exact match

**Now let’s take a look at the formula after we combine Vlookup & Match**

In screenshot4, we have some** Car SalesData**.

We have used **Data Validation** in cell B9 for **Cities** & in cell C8 for **Sales Person.**

In Cell C9, the Vlookup function is used with the Match functionto return the number of Car Sales for **‘Steve’** in **‘Alabama’** City using fixed **Index number** as 2.

With a combination of Vlookup and Match Formulae, the Vlookup formula works **dynamically** refer screenshot4

**Screenshot4**

We need to -

- To search for a name in cell B9 i.e. Alabama, within the range B2:F6.
- Match cell C8 i.e. Steve in range B2:F2.
- Return the Column Index Number as 2 as shown in cell C11

The formula to accomplish how many Cars are sold by Steve in Alabama City is:

**C9=VLOOKUP(B9,$B$2:$F$6,MATCH(C8,$B$2:$F$2,0),0)**

“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”

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

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

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

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.

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

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

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?

sir plz give me some Result sheet formula

“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”

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

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.

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

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

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?