How to use VLOOKUP, INDEX, ... in Excel you should know

The functions VLOOKUP, INDEX, MATCH, ... are quite complex functions but are effective in data mining in Excel . In this article, I will guide you in detail how to use VLOOKUP and related functions effectively.

1. VLOOKUP function

Usage : Finds a specified value in the first column and pulls matching data from the same row in another column. This is one of the most popular functions and provides great support for complex office tasks.

Formula : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

In which :

+ Lookup_value : The value to search.

+ Table_array : Two or more columns of data.

+ Col_index_num : Number of columns to pull data.

+ Range_lookup : Determine whether the search is completely accurate (FALSE) or just relatively accurate (TRUE or omitted).

Example : We use the VLOOKUP function to find the Bonus of each employee. The value to look for is Cell Position C2; The array to detect is the array F1: G3. When entering the formula = VLOOKUP (C2, $ F $ 1: $ G $ 3,2,0) (1) the result is STAFF 2000 and ROOMER 5000 (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

2. INDEX function

Usage : Returns a reference to a cell inside an array based on the number of rows and columns you specify.

Formula : = INDEX (array, row_num, [column_num])

Of which : Array (required): A cell range or an array constant.

 

 

Note :

+ If the array has many rows and more than one column and only row_num or column_num is used, the index will return an array of the entire row or column in the array.

+ Row_num (required, unless column_num is present) : Selects the row in the array from which to return a value. If row_num is omitted, column_num is requested.

+ Column_num (optional) : Select the column in the array from which to return a value. If column_num is omitted, row_num is requested.

 

 

 

Example : When we need to get the value of the first row, second column in the table, we use the INDEX function . Array is the array we need to detect is B2: C5; Row_num is line 1; Column_num is column 2. When entering the formula INDEX (B2: C5,1,2,1) (1) the result is "HEAD OF ROOM" (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

3. MATCH function

Usage : Looks up a specific value in a range of cells, and shows the relative position of that value.

Formula : = MATCH (lookup_value, lookup_array, [match_type])

In which :

+ lookup_value (required) : The value you want to match in the search array. Change the lookup value number can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

+ lookup_array (required) : The range of cells to be searched.

+ match_type (optional) : Number -1, 0, or 1. The match type argument specifies how Excel matches the lookup value to values ​​in the lookup array. The default value for this argument is 1.

For example : When we need to know what position of CLOTH is in, we use MATCH function . When entering the formula = MATCH (FF2 & "*", "B2: B7", 0) (1), the result is 3 (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

3. INDIRECT function

Usage : Returns a cell reference or range specified by a text string. Use the INDIRECT function when you want to change the reference to a cell in a formula without changing the formula itself.

Formula : = INDIRECT (ref_text, [a1])

In which :

+ Ref_text (required) : Reference to a cell containing the A1 reference style, the R1C1 reference style or a reference to the cell as a text string.

+ If the reference text is not a valid cell reference, INDIRECT returns the #REF! Error value . .

+ If ref_text refers to another workbook (an external reference), that workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! Error value . .

 

 

Note :

External references are not supported in Excel Online.

+ This behavior is different from versions of Excel earlier than Microsoft Office Excel 2007, which will ignore the excess limit and return a value.

 

 

For example : When you only need to calculate the commission and you want to drag, drop or move the result box to another position without changing the money formula, we use INDIRECT . When entering the formula

= INDIRECT (“B2”, TRUE) * INDIRECT (“C2”, TRUE) (1) will give the result 700 * 100 = 70000 (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

4. OFFSET function

Usage : Returns the reference to a range by a specified number of rows and a specified number of columns from a cell or range of cells. The returned reference can be a single cell or a range of cells. You can specify the number of rows and columns to return.

Formula : = OFFSET (reference, rows, cols, [height], [width])

In which :

+ Reference (required) : The reference area that you want to base the reference distance on. The reference range must refer to a cell or a range of adjacent cells; otherwise OFFSET returns the #VALUE! error value . .

+ Rows (required) : The number of rows, up or down, that you want the upper-left cell to refer to. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).

+ Cols (required) : Number of columns, to the left or right, that you want the cell in the upper left corner of the result to refer to. Columns can be positive or negative.

+ Height (optional) : The height, in number of rows, that you want to have for the returned reference. The height must be a positive number.

+ Width (optional) : The width, in the number of columns, that you want to have for the returned reference. Width must be a positive number.

Example : We use OFFSET to localize values, and then use the SUM function to calculate the sum. The cell we refer to is A1; compared to A1, the Rows of the reference range is below 1 cell, so Rows is 1; compared to A1, the left Cols is 1 cell, so Cols is 1; Height we take 3 lines; Width we get 2 columns. So the result when adding up all the values ​​in the region is 2025.

How to use VLOOKUP, INDEX, ... in Excel you should know

5. TRANSPOSE function

Usage : Converts a horizontal range of cells into a vertical range and vice versa, i.e. converts rows into columns and columns to rows.

Formula : = TRANSPOSE (array)

Doing

+ Step 1 : Select blank cells.

+ Step 2 : Enter = TRANSPOSE (B2: C4).

+ Step 3 : Enter the range of original cells.

+ Step 4 : Finally, press CTRL + SHIFT + ENTER.

Example : When we need to convert the whole product price list from vertical to horizontal, we use the TRANSPOSE function with the array of values ​​B2 to C4. When entering the formula = TRANSPOSE (B2: C4) (1) will give the result as shown (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

6. HYPERLINK function

Uses : Creates a hyperlink to a document stored on the local network or the Internet.

Formula : = HYPERLINK (link_location, [friendly_name]).

In which :

Link_location can refer to a location in a document, like a specific cell or named range in a worksheet or in an Excel workbook, or to a bookmark in a Microsoft Word document.

The path can be to a file stored on the hard drive. The path can also be a universal naming convention (UNC) path on the server (in Microsoft Excel for Windows).

 

 

 

Note :

+ Link_location can be a text string between quotation marks or a reference to a cell containing a link as a text string.

+ Friendly_name can be a value, text string, name or cell containing jump text or value. If friendly_name returns an error value (for example #VALUE!), The cell displays an error instead of the jump text.

 

 

 

 

Example : Links that are long and confusing will be annoying to users. So please use HYPERLINK to make the interface easier to use. Entering the formula = HYPERLINK (A1, “LINK GOOGLE”) (1) will result in LINK GOOGLE (2).

How to use VLOOKUP, INDEX, ... in Excel you should know

The above article guided VLOOKUP and related functions. Hope that the above article will be useful to you.

Sign up and earn $1000 a day ⋙

How to hide apps on Samsung Galaxy J3 Pro

How to hide apps on Samsung Galaxy J3 Pro

The feature allows you to hide apps that you want to keep from being seen by others.

Measure heart rate on Samsung Galaxy S7

Measure heart rate on Samsung Galaxy S7

Heart rate monitor on Samsung Galaxy S7 helps you to measure your current heart rate at all times.

Enable Multi Wallpaper on Samsung Galaxy S8

Enable Multi Wallpaper on Samsung Galaxy S8

The feature allows the wallpaper to change every time you open the lock screen.

Factory Reset Samsung Galaxy Tab A6 10.1

Factory Reset Samsung Galaxy Tab A6 10.1

Factory reset helps you to reset all settings of your tablet to factory defaults.

Set to vibrate on keyboard touch on Samsung Galaxy A7

Set to vibrate on keyboard touch on Samsung Galaxy A7

Set to vibrate on keyboard touch on Samsung Galaxy A7

Samsung Galaxy J7 Pro battery percentage display

Samsung Galaxy J7 Pro battery percentage display

Samsung Galaxy J7 Pro battery percentage display makes it easy to keep an eye on your device's power consumption.

How to connect OTG on Samsung Galaxy Tab A6 10.1

How to connect OTG on Samsung Galaxy Tab A6 10.1

You can use the OTG connection to connect to other external peripherals such as a mouse, keyboard or USB.

How to take screenshots on Samsung Galaxy Note 8

How to take screenshots on Samsung Galaxy Note 8

Screen capture on Samsung Galaxy Note 8 helps you capture images at the screen you are working on.

Update Android software on Samsung Galaxy Tab E 9.6 SM-T561

Update Android software on Samsung Galaxy Tab E 9.6 SM-T561

Software updates help the machine add new features or improve the machine's performance in the user experience.

What is Zalo Media Box? How to delete, block messages from Zalo Official Account

What is Zalo Media Box? How to delete, block messages from Zalo Official Account

Zalo Media Box brings useful information to Zalo users, but sometimes it makes users feel uncomfortable. So what is Zalo Media Box? If I do not want to be disturbed, how to delete and block messages from Zalo Official Account? Let's find out!

Learn about Intel Core i7 10875H processor, what are the advantages and disadvantages?

Learn about Intel Core i7 10875H processor, what are the advantages and disadvantages?

A series of 10th generation Intel CPUs were released, with the Intel Core i7-10875H one of the most powerful upgrades in terms of performance. Let's learn about this processor together.

Check the number of printed pages printed and the number of remaining pages in the toner cartridge

Check the number of printed pages printed and the number of remaining pages in the toner cartridge

Along with the modern life rhythm today, the use of modern printers is no longer a strange thing for everyone, especially office workers. So to check how many pages have printed and how many pages are left of the cartridge, let WebTech360 help you!

A beginners guide to using Apple Watch from A to Z

A beginners guide to using Apple Watch from A to Z

If you've just owned an Apple Watch and don't know how to use it, learn more through the article below. The article will guide you how to use Apple Watch from A to Z simply and easily.

6 ways to treat when Instagram does not show sticker, lose simple filter

6 ways to treat when Instagram does not show sticker, lose simple filter

Sticker (sticker) and filter (color filter) are specialties of the social network Instagram. However, sometimes you will run into the problem of losing stickers and filters on this app. So how to fix? Follow the article below to get the answer!

How to remove a wristwatch without tools is simple and easy to do

How to remove a wristwatch without tools is simple and easy to do

Removing the back of the watch to replace the Battery or inspect the watch's internal components at home saves you time and money than taking it to the store. Follow along for instructions on how to remove the back (back cover) of your watch without tools!

Learn about integrated graphics card Intel Iris Xe Graphics

Learn about integrated graphics card Intel Iris Xe Graphics

Today, the equipment of integrated graphics cards on laptops is gradually gaining popularity because it saves space for people who move around with their laptops. So Intel released a new integrated graphics card Intel Iris Xe Graphics of Intel Gen 11 processor, let WebTech360 learn more about this card!

How to identify genuine Oakley glasses

How to identify genuine Oakley glasses

Oakley is a famous brand of sports eyewear. With luxurious designs, high quality and reasonable prices, Oakley glasses are popular with many people, especially in the sports world. Therefore, counterfeit, imitation Oakley products appeared on the market. Today, I will show you how to recognize genuine, genuine Oakley glasses

What is GMT clock? Instructions on how to view GMT clock properly

What is GMT clock? Instructions on how to view GMT clock properly

The GMT watch is a device trusted by business people, pilots and adventurers. So what is GMT clock? How to see this kind of watch like? You follow the post to find out!

The formula for calculating the exact area of ​​a rhombus, perimeter of a rhombus

The formula for calculating the exact area of ​​a rhombus, perimeter of a rhombus

The following article will introduce to you the formula to calculate the area of ​​a rhombus, the perimeter of a rhombus most accurately. Stay tuned.

Instructions on how to use and maintain the most durable gold-plated watch

Instructions on how to use and maintain the most durable gold-plated watch

Owning a gold-plated watch is the dream of many fashion watchmakers. So if you already own a gold-plated watch, do you know how to use and preserve the most durable gold-plated watch?