Use VLOOKUP and INDEX/MATCH in controlling

Getting an overview of effective methods for searching and outputting data in Excel can significantly simplify everyday work. In this guide, you will learn how to use VLOOKUP as well as the combination of INDEX and MATCH to retrieve information such as the name or price based on the article number in extensive data lists. This is particularly important for controlling and sales, where quick and precise data analyses are required.

Main Insights

• VLOOKUP searches data in a matrix from left to right, while INDEX and MATCH allow flexible searching in any direction.
• VLOOKUP can be used for simple queries but reaches its limits with more complex requirements such as searching to the left.
• INDEX and MATCH provide a powerful alternative for analyzing data from different directions.

Step-by-Step Guide

Using VLOOKUP

To start with VLOOKUP, open the Excel file in which you want to work. First, you need to define the search criteria, such as an article number.

Enter the article number in the search field. For this example, we are using the article number "101," which stands for "Phone X." The VLOOKUP will then be applied to this article number.

To insert the VLOOKUP, go to a cell and use the formula =VLOOKUP(...). The first parameter is the search criteria, which is the article number you just entered.

Next, you need to specify the matrix in which to search. This includes all relevant columns where the article numbers and their corresponding labels are located.

Now you need to specify the column index you want to return. In our example, index "2" returns the name and index "3" returns the price.

The final parameter determines the type of match. For an exact match, like in this case, you put "FALSE."

After entering the formula, you should see the name of the article "Phone X" if everything is correct.

Using INDEX and MATCH

Now we move on to INDEX and MATCH. You can use the same search and logic but this method offers more flexibility. Again, select the cell where you want to enter the formula.

Start with the formula =INDEX(...) and specify the range from which you want to retrieve the data. In this case, we are using the article names.

In the next step, use MATCH to find the exact position of the article number. The formula for this is MATCH(...), where you again enter the article number and the range of article numbers.

Now combine both formulas. INDEX requires the row number returned by MATCH, so you then have the complete formula:

=INDEX(Range,MATCH(SearchCriterion,Range,0))

If everything has been entered correctly, you should now see "Handy X" in the cell, and the method works just as well as VLOOKUP.

Flexibility in searching to the left

A major advantage of INDEX and MATCH is the ability to search to the left. For example, if you want to query the price of an item, you can specify the price range and compare the item number again.

Entering the formula =INDEX(priceRange,MATCH(searchCriterion,itemNumberRange,0)) simplifies and speeds up many processes.

With these setups, you can create beautiful lists that you can customize as you like. This table design not only helps you, but is also useful for employees who need quick access to important data.

Conclusion

This guide provides you with the basic skills to effectively use VLOOKUP and INDEX/MATCH in Excel to selectively retrieve data from large lists. These tools are excellent for controlling and sales.