Tag Archive: VLOOKUP

Articles sur le thème : VLOOKUP

Calculate between two values in a range

In Excel, you can easily return a result between two values. Most of the people think that they have to do a lot of IF nested. But you can do the same job with the function VLOOKUP What is the problem? Let's say you have this workbook where you want to calculate the commission for …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/calculate-between-two-values/

Excel could be a database - VLOOKUP

Once you have a table with one or more columns, you can use VLOOKUP to returns one of the values in this table by referring to contents of first column. Principle of the function VLOOKUP Excel can be used as a database. Often, Excel files store data like customers, prospects or products and you want …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/use-excel-like-database-vlookup/

Function MATCH

The MATCH function returns the position of a value in a range of cells This function is very useful to create dynamic research with the function VLOOKUP or INDEX. Presentation of the MATCH function MATCH function only needs two mandatory parameters and an optional parameter The researched value Reference data (a range of cell) with …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/function-match/

Vlookup to the left in Excel

Problem of the VLOOKUP function The normal behavior of the VLOOKUP function is to return data from a table. But you can only return data on the right of the column containing the ID. However, in some cases, you can not change the order of the columns. Like for instance in this document where the …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/vlookup-to-the-left-in-excel/

Create an Offset Vlookup

In the case you have a table that contains the same ID for many - or empty cells. It is strongly recommended to reorder your data To perform this modification, we will use 3 functions , INDEX , MATCH and OFFSET. Problem to solve We have a table (column A:D ) with the list of sales …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/create-an-offset-vlookup/

Compare 2 columns

How to compare 2 columns in Excel?  It's very simple 😄😎👍 I will show you in this post how to do this with the functions VLOOKUP for the research ISNA for the test IF to customize the final result 😉 But first, let's start with a live demo 💛❤💙💜💚 Live demo In this worksheet (yes, it's …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/compare-2-columns/

Anonymise your data

If you work on a workbook with confidential data, you need to anonymise your data if you collaborate with other people. The technique is not really complex but you have to respect the following steps. The initial document Imagine you are a journalist and you receive the following file in your mailbox (it's all fake data). …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/anonymise-your-data/