Never go wrong while looking for something

Arun Kr. Khattri
LibreCalc
Page content

Never go wrong while looking for reference value

When you are looking for something in a single row or column and like to reference the value from the same position in different row or column, there are number of options available to you in LibreCalc. You may try LOOKUP, HLOOKUP, VLOOKUP and OFFSET depending upon the use case, as each of them has certain requirement to be adhered while using them. My favourite is to mix INDEX and MATCH It allows to look in rows, columns, or both at the same time. Together they are the most potent tools to look and reference in any spreadsheet application.

INDEX function

INDEX return a subrange, specified by row and column number, or an optional range index. Depending on context, INDEX returns a reference or content.

Syntax:

INDEX(Reference[; Row[; Column[; Range]]])
  • Reference: reference, range or range name. If the reference consists of multiple ranges, one must enclose the references or range name in parentheses.
    • If reference is a one-dimensional Column vector, Column is optional or can be omitted as an empty parameter (;;).
    • If reference is a one-dimensional Row vector, Row is optional or can be omitted as an empty parameter (;;).

Example

Take for example, if we are supplying range -

INDEX function

Figure 1: INDEX using range

Another example using single column -

INDEX using single column

Figure 2: INDEX using single column

MATCH function

The function give the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup array as a number.

Syntax:

MATCH(SearchCriterion; LookupArray[; Type])
  • SearchCriterion: value that is to be searched for.
  • LookupArray: reference (range or range name)
  • Type: may take the values 1, 0 or -1, if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order.
    • 1, or parameter missing, The index of the last value that is smaller or equal to the search criterion is returned.
    • 0, only exact matches are found, if found more than once, returns the index of first matching value.
    • -1, it is assumed that the column is sorted in descending order, the first value that is larger or equal is returned.

Example

Suppose we want to know the position of product_C in the data …

MATCH function

Figure 3: MATCH

its on the 3rd row in our reference(column vector)

Combining the power of INDEX and MATCH

moving ahead with our quest to find ultimate solution for lookup, let’s combine both the function and find Rate of product_C in our example data…

index and match function

Figure 4: INDEX + MATCH

  • start with INDEX
  • select Rate range (D5:D18)
  • to provide row number take help of MATCH function. Enter MATCH,
  • provide search criterion, value to be searched is product_C,
  • for LookupArray provide range where your search criterion is i.e B5:B18
  • as we are looking for exact match, for Type enter 0.

₹60.00 is the rate for product_C.

Take another example for Two-way lookup with INDEX and MATCH, in the above example we omitted column number as we gave column vector as reference to index. As we used match to find row number, this time we will use it twice - first to get row reference and then column reference.

two-way index and match function

Figure 5: Two-way MATCH and INDEX

For Case-sensitive lookup we have to take help of EXACT function to return case sensitive match, see the below example for clarity

case-sensitive lookup

Figure 6: Case-sensitive lookup

INDEX and MATCH together is incredibly flexible. It’s a kind of swiss knife for lookup.