How to Use VLookup and Match formulas in Excel

In this article, we will learn how to use ISERROR function along with Vlookup function to ignore the error as result in Microsoft Excel.

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.

  • As we know, we use Vlookup formula to pick the value from one data to another data by using the common value.
  • Match function is also a lookup function. This formula will help us match the column value.

 

Let’s take an example and understand how we can use Match function along with Vlookup function and how it works?

We have car sales data in which we have used Data Validation in cell A15 for Cities & in cell B14 for Sales Person.

In Cell B15, the Vlookup function is used with the Match function to 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.

image 1

 

Follow below given steps:

  • To search for a name in cell B9 i.e. Alabama, within the range A7:E11
  • Match cell B14 i.e. Steve in range A7:E7
  • The formula to accomplish “how many” Cars are sold by George in Florida City is:
  • A15= =VLOOKUP(A15,A7:E11,MATCH(B14,B7:E7,0),FALSE)

image 3

 

Whenever you will change the city name and sales name, formula will pick the number accordingly from the data.

home button
Next VLOOKUP with ISNA function

 
 
image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Users are saying about us...

  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. 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.

  5. 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

  6. 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

  7. "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"

  8. 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

  9. 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.

  10. 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

  11. 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

  12. 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 *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube