Excel functions for working with links and arrays

In this example, you will learn all about the Excel functions for working with links and arrays, such as VPR, GPR, MORE EXPOSED, INDEX и SELECTION.

VPR

Function VPR (VLOOKUP) looks up the value in the leftmost column of the table and returns the value of the cell in the specified column of the same row.

1. Inserting a function VPR:

   =ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)

   =VLOOKUP(A2,$E$4:$G$7,3,FALSE)

   Explanation:

   • Function VPR looking for a value ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value from the third column of the same row (since the third argument of the function is 3).
   • The fourth argument of the function is LYING (FALSE) – this means that either an exact match will be found, or an error message will be displayed #N/A (#N/A).
2. Drag mouse to copy function VPR from cell B2 down the column to the cell B11.Explanation: When we copy a function VPR down, absolute link $E$4:$G$7 remains unchanged, while the relative reference A2 changes to A3, A4, A5 and so on.

GPR

The function works in a similar way. GPR (HLOOKUP):

MORE EXPOSED

Function MORE EXPOSED (MATCH) returns the position of the searched value in the given range:

Explanation:

• Word Yellow occupies the third position in the range E4: E7.
• The third function argument is optional. If you enter a value for this argument 0 (zero), then the function will return the position of the element that exactly matches the searched value (A2). If an exact match is not found, the function will return an error. #N/A (#N/A).

INDEX

Function INDEX (INDEX) returns a given value from a two-dimensional or one-dimensional range.

Explanation: Meaning 92 is at the intersection of the line 3 and column 2 in the range E4:F7.

Explanation: Meaning 97 is located 3 place in the range E4: E7.

SELECTION

Function SELECTION (CHOOSE) selects a value from the list at the given position number.

Explanation: Word Boat is in position 3.