Étiquette : lookup and reference function

The TRANSPOSE function is used to switch the orientation of a given range or array – converting vertical ranges to horizontal and vice versa.

Syntax:

=TRANSPOSE(array)

Argument:

• array (Required):
  The range of cells to be transposed. When transposed:
  • The first row becomes the first column of the new array
  • The second row becomes the second column
  • This pattern continues for all rows/columns

USING THE TRANSPOSE FUNCTION

Example: Convert Vertical Range to Horizontal

Original Vertical Range (A1:B5)

Steps to Transpose:

1. Select blank cells in a horizontal arrangement matching the original range’s dimensions (e.g., 2 columns × 5 rows → select 5 columns × 2 rows)

1. Enter the formula:

=TRANSPOSE(A1:B5)

1. Press Ctrl+Shift+Enter (to enter as an array formula)

Result:

IMPORTANT NOTES:

1. Array Formula Requirement:
   • Must be entered with Ctrl+Shift+Enter (not just Enter)
   • Curly braces {} will appear around the formula
2. Editing Restrictions:
   • Cannot modify part of the transposed array
   • To edit: Select entire transposed range → Make changes → Re-apply with Ctrl+Shift+Enter
3. Range Dimensions:
   • Target range must have inverse dimensions of source range
   • (e.g., 3 rows × 2 columns → needs 2 rows × 3 columns blank cells)
4. Dynamic Arrays (Excel 365):
   • Newer versions don’t require Ctrl+Shift+Enter
   • Automatically spills results

The MATCH function is used to search for a specified value within a range of cells and returns the relative position of that value within the range.

Syntax:

=MATCH(lookup_value; lookup_array; [match_type])

Arguments:

1. lookup_value (Required):
   • The value you want to find within the lookup_array.
2. lookup_array (Required):
   • The range of cells being searched.
3. match_type (Optional):
   • Specifies how Excel matches the lookup_value with values in the lookup_array.

match_type	Behavior
1 or omitted	Finds the largest value ≤ lookup_value. Requires lookup_array to be      sorted in ascending order.
0	Finds the first exact match. Does not require sorting.
-1	Finds the smallest value ≥ lookup_value. Requires lookup_array to be sorted in descending order.

USING THE MATCH FUNCTION

Example: Find the Position of « Apple »

Given the following table:

1. Select an empty cell and enter

=MATCH(B3; A3:A6; 0)

(Where B3 contains « Apple »)

1. Press Enter → Returns 4 (Apple is the 4th item in the range).

NOTES & ERRORS

• Case-Insensitive: Does not distinguish uppercase/lowercase.
• #N/A Error: Occurs if no match is found.
• Wildcards Supported: Use * (any sequence) or ? (single character) for partial matches.
• Exact vs. Approximate: Use match_type=0 for exact matches; 1 or -1 for approximate (requires sorted data).

 

The CHOOSE function selects and returns a value from a list based on a specified index number.

The syntax for the CHOOSE function is as follows:

=CHOOSE(index_num; value1; [value2]; …) 

Arguments:

• index_num (Required):
  • An integer between 1 and 254 that indicates which value to return.
  • Can be a number, formula, or cell reference.
• value1, value2, … (value1 required, others optional):
  • A list of up to 254 values from which CHOOSE selects.
  • Values can be numbers, text, formulas, cell references, or defined names.

USING THE CHOOSE FUNCTION

Consider the following list of names in cells A2:A5:

 

Example: Return the 3rd Value (Jasmine)

1. Select an empty cell and enter:

=CHOOSE(3; A2; A3; A4; A5) 

1. Press Enter—the function returns Jasmine (the 3rd value).

NOTES & ERROR HANDLING

• Text values must be in quotes, or Excel returns #NAME?.
• #VALUE! Error occurs when:
  • index_num is <1 or >number of values provided.
  • index_num is non-numeric (e.g., text).

The HLOOKUP function (where « H » stands for Horizontal) is used to search for a specific value in the top row of a table or dataset and retrieve corresponding data from another row in the same column.

The syntax for the HLOOKUP function is as follows:

=HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup]) 

Arguments:

• lookup_value (Required): The value to search for in the first row of the table.
• table_array (Required): The range of cells containing the data to be searched.
• row_index_num (Required): The row number (within the table array) from which to return the matching value.
• range_lookup (Optional): Specifies whether to find an exact match (FALSE) or an approximate match (TRUE).
  • If TRUE (or omitted), it finds the closest match (or next largest value if no exact match exists).
  • If FALSE, it returns an exact match or #N/A if no match is found.

USING THE HLOOKUP FUNCTION

Consider the following example table containing student names and their scores in different subjects. We will use HLOOKUP to find Joy’s score in Mathematics.

 

Steps to Find Joy’s Math Score:

1. Select an empty cell and begin the HLOOKUP function with the lookup_value (e.g., cell B1 containing « Joy »):

=HLOOKUP(B1; 

1. Define the table_array (the range where data is stored, e.g., A1:E5):

=HLOOKUP(B1; A1:E5;

1. Specify the row_index_num (the row containing the return value, e.g., 3 for « Math »):

=HLOOKUP(B1; A1:E5; 3;

1. Choose between exact or approximate match (use FALSE for exact, TRUE for approximate):

=HLOOKUP(B1; A1:E5; 3; FALSE) 

1. Press Enter, and the result should be 59 (Joy’s Math score).

NOTES WHEN USING THE HLOOKUP FUNCTION

• Like VLOOKUP, HLOOKUP is not case-sensitive (treats « JOY » and « joy » the same).
• Wildcards (*, ?, ~) can be used for partial matches.
• Only the first matching value is returned if duplicates exist.

Common Errors:

• #N/A! → No match found when range_lookup is FALSE.
• #REF! → Occurs if row_index_num exceeds the table’s row count.
• #VALUE! → Occurs if:
  • row_index_num is less than 1.
  • Non-numeric row_index_num is provided.

The VLOOKUP function (where « V » stands for Vertical) is used to quickly search for a specific value in the first column of a table or dataset and retrieve corresponding data from another column in the same row.

The syntax for the VLOOKUP function is as follows:

=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup]) 

Arguments:

• lookup_value (Required): The value to search for in the first column of the table.
• table_array (Required): The range of cells containing the data to be searched.
• col_index_num (Required): The column number (within the table array) from which to return the matching value.
• range_lookup (Optional): Specifies whether to find an exact match (FALSE) or an approximate match (TRUE).
  • If TRUE (or omitted), it finds the closest match (or next largest value if no exact match exists).
  • If FALSE, it returns an exact match or #N/A if no match is found.

USING THE VLOOKUP FUNCTION

Consider the following example table containing 4 fruits and their prices. We will use VLOOKUP to find the price of Yam.

Steps to Find the Price of Yam:

1. Select an empty cell and begin the VLOOKUP function with the lookup_value (e.g., cell A12 containing « Yam »):

=VLOOKUP(A12; 

1. Define the table_array (the range where data is stored, e.g., A4:C8):

=VLOOKUP(A12; A4:C8,

1. Specify the col_index_num (the column containing the return value, e.g., 3 for « Price »):

=VLOOKUP(A12; A4:C8; 3

1. Choose between exact or approximate match (use FALSE for exact, TRUE for approximate):

=VLOOKUP(A12; A4:C8; 3; FALSE) 

1. Press Enter, and the result should be 54 (the price of Yam).

NOTES WHEN USING THE VLOOKUP FUNCTION

• If range_lookup is omitted, VLOOKUP defaults to TRUE (approximate match).
• If duplicate values exist, VLOOKUP returns only the first match.
• VLOOKUP is not case-sensitive (treats « APPLE » and « apple » the same).
• Wildcards (*, ?, ~) can be used for partial matches.

Common Errors:

• #N/A! → No match found for the lookup value.
• #VALUE! → Occurs if:
  • col_index_num is less than 1 or non-numeric.
  • range_lookup is not TRUE/FALSE.
• #REF! → Happens when:
  • col_index_num exceeds the table’s column count.
  • A referenced cell in the formula does not exist.