Excel cross referencing and VLOOKUP

Excel cross referencing is a feature that allows you to link data, like product data, from one table or range to another. There are several methods for cross referencing in Excel, but one of the most widely used methods is the VLOOKUP function. Although it is used widely, this method has some large drawbacks.

Automate VLOOKUP without limitations

Excel cross referencing is usually done with VLOOKUP. It certainly is a useful tool, but users are also confronted with the limitations when they want to automate VLOOKUP in Excel:

The function does not recognize synonyms or abbreviations.
VLOOKUP can’t deal with small mistakes in product numbers, for example: if you VLOOKUP product number 4015-0800/ and in your database it is correctly stored as 40150800 VLOOKUP won’t be able to cross reference this product number.
VLOOKUP does not have the ability to give alternative or similar options, for example if you are looking for a certain product with certain attributes VLOOKUP is not ‘’smart’’ since it does not use advanced algorithms or machine learning. It is not able to give you recommendations based on the information you put in.
Often you will deal with the #NA error in VLOOKUP. In big data sets it is very hard to identify the leading or trailing spaces in lookup values that cause the VLOOKUP function to not find the match and return #NA error.

Why should I use VLOOKUP to cross reference in Excel?

If you are trying to match or deduplicate data VLOOKUP and VLOOKUP in bulk could potentially help you out. To make use of VLOOKUP successfully a major factor is the quality of your product data. The VLOOKUP function allows you to search for a value in one table and return a related value from another table, this can also be done in bulk so you can match or deduplicate data at a larger scale.

How to use VLOOKUP and VLOOKUP in bulk?

To use VLOOUK in Excel, you need to be a frequent user. The basic syntax (format, structure) for VLOOKUP looks like this:

‘’=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)’’

For example, suppose you have two tables: one table contains a offer request sent by your customer, with a product number and their product information and another table contains your product catalogue listed with your product information. With Excel cross referencing you can use the VLOOKUP function to compare the two tables and find the product your customer is asking for.

To perform VLOOKUP in bulk, you will need to use an array formula. An array formula is a formula that performs a calculation on multiple values at once.

The syntax for VLOOKUP in bulk is as follows:

‘’=ARRAYFORMULA(VLOOKUP(lookup_values,table_array,col_index_num,range_lookup))’’

The lookup_values is the range of cells containing the values you want to look up. This can be a single column or multiple columns. The table_array, col_index_num, and range_lookup arguments are the same as in the regular VLOOKUP formula.

Is Excel cross referencing with VLOOKUP in Bulk the right way?

In the end this is only a question you can answer. Like mentioned, VLOOKUP in Bulk will not be able to cross reference a product when data contains small errors like for example stripes, spaces and slashes. It also requires a lot of manual actions and Excel expertise.

If you would just like to drag-and-drop your Excel file with product numbers and match or deduplicate your data with your own product catalogue you could consider working with a software provider like Mydatafactory.

Mydatamatcher: the world class matching engine!

For product data matching Mydatafactory has a developed a world class matching engine which can deal with synonyms and errors like small mistakes in data and product numbers. The solution, Mydatamatcher:

Speeds up the process by ten till twenty-fold
Is user friendly
Gives you the ability to deal with more complex matching challenges
Reduces 99% of the errors during the cross-referencing process
Up and running in less than two weeks

Excel cross referencing and VLOOKUP

Automate VLOOKUP without limitations

Why should I use VLOOKUP to cross reference in Excel?

How to use VLOOKUP and VLOOKUP in bulk?

Is Excel cross referencing with VLOOKUP in Bulk the right way?

Mydatamatcher: the world class matching engine!

Want to see how Mydatafactory automated cross referencing and product matching?

Product

Resources

Company

Connect with us