SoniceResearches - 5 tips to know for your Excel Program

Four methods you should know to access your data in tabular form in MS-Excel

August 25 - 2021

MS-Excel is one of the most used software in every technical field all around the world. If any company uses computers, they will most probably use MS-Excel. MS-Excel can perform from simple addition to complex derivations. MS-Excel consists of working space with rows and columns, and hence tabular arrangement of data (matrix) is widely used. The following methods will ease up managing those data to a great extent.

1. HLOOKUP and VLOOKUP

HLOOKUP and VLOOKUP function performs the same task - extracting a required value from a range (matrix), but with a different mechanism.

To extract value 2.6 from the range,

= VLOOKUP(1.8, A1:E6, 3, FALSE)

= HLOOKUP (2, A1:E6, 4, FALSE)

In the table in Figures 1 and 2, A1:E6 is the array/matrix, from where data is to be extracted. While using VLOOKUP, the lookup value must be the value in the first column of the matrix, while in HLOOKUP, the lookup value must be the value in the first row of the matrix. Finally, the numbering of the column index is as shown in figure 1. For value 2.6, the column index is 3. Similarly, for HLOOKUP, the row index is numbered, as shown in figure 2. For value 2.6, the row index is 4. The last parameter required denotes the type of match, where TRUE means approximate match and FALSE means exact match.

Remember:

Both VLOOKUP and HLOOKUP take the first match in case of multiple criteria.
These functions can take multiple criteria if used appropriately with the MATCH function.

Figure 1. How VLOOKUP works

Figure 2. How HLOOKUP works

2. INDEX

INDEX allows you to access the data anywhere in the range/matrix. In figure 3, A1:E6 is the matrix, and 2.6 is the targeted value. To access this value, we need to find its row and column index, which is 4 and 3 respectively. Hence, the required function is

= INDEX (A1:E6, 4, 3)

Note:

INDEX function can be used in-place of VLOOKUP or HLOOKUP functions if used appropriately with the MATCH function. The advantage of using INDEX is that the lookup value is, no longer, constrained in the first row (HLOOKUP) or the first column (VLOOKUP)

Figure 3. How INDEX works

3. OFFSET

OFFSET is widely used for both - accessing a specific value or a whole range. This function takes five parameters - origin, row_shift, col_shift, no. of rows, no. of columns. Let's understand this function with the following two examples.

Single value access example.

Again lets take the same range, A1:E6. With the first three parameters, our target will be to reach our target value - 2.6. We can do that with:

Origin - A1, Row_shift = 3 (3 Down), Col_shift = 2 (2 Right)

Origin = C3, Row_shift = 0 (0 Down), Col_shift = 0 (0 Right)

Origin = E6, Row_shift = -2 (2 Up), Col_shift = -2 (2 Left)

Finally, no of rows and columns of required range = 1 row, 1 columns. Hence, to access 2.6, all three formulas will work.

= OFFSET(A1, 3, 2, 1, 1), or = OFFSET(A1:E5, 3, 2, 1, 1)

= OFFSET(C4, 0, 0, 1, 1) ,or = OFFSET(C4, , , 1, 1)

= OFFSET (E6, -2, -2, 1, 1)

Range access example

The OFFSET function is used for range selection, which can be used in other functions where range data is required, such as INDEX or VLOOKUP. Just as in a single value, the first three parameters are used to reach the origin/first value of the target range. Then for the last two parameters, we require the dimension of the target array. For example, to access C4:D5 (Origin = C4, Size = 2 x 2),

= OFFSET (A1,3,2,2,2)

= OFFSET (C4,0,0,2,2)

= OFFSET (E6,-2,-2,2,2)

= OFFSET (D5,0,0,-2,-2)

Figure: OFFSET-Single Value Access

[Circle - Origin; Arrows - Shifts; Rectangle - Target Array(1 row, 1 col)]

Figure: OFFSET-Range Access

[Circle - Origin; Arrows - Shifts; Rectangle - Target Array(2 row, 2 col)]

4. Excel VBA

Last but not least, another method to access tabular data in Excel is VBA programming. Excel VBA is the programming section of MS-Excel. Learning VBA is tough, but it makes Excel way more powerful. With VBA, we can create user-defined functions, perform repetitive tasks on single click, to name a few.

Page updated

Google Sites

Report abuse