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.
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)
Whenever you will change the city name and sales name, formula will pick the number accordingly from the data.
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 firstname.lastname@example.org