Search

FINDITEM

Finds a valid item in a list or time period list by matching a text string or code to the list items. Takes a list as first parameter and name or code text string as second parameter and matches the text string to the list items. You can use FINDITEM to search a list and return a matched list item.

Syntax

FINDITEM(x, y) 

where:

  • x: name of a list or a time period list
  • y: text-formatted line item.

Format

Input Format Output Format

x: List, Time

y: Text

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: Text: Text-formatted line item, text constant, or general expression.
  • y: Text: Text-formatted line item, text constant, or general expression.

Constraints

The function has the following constraints:

  • First parameter must be the name of a list or a time period list for the result of the search.
    • List: the result line item must be list-formatted for the list you are going to search.
    • Time period: the result line item must be time period formatted and use the same granularity as the list you are searching. For example, if you are searching a list formatted as Months, the result line item must be time period formatted as Months.
  • The second parameter must be text-formatted for entering the name or code text string you want to match to a list item.
Tip: If you have a list containing numbers, and wish to find a number in that list, use the TEXT function. Anaplan stores lists in text format, so the numbers in the list will be converted to text. If you use a number in the y argument, it must be converted to text.

Excel equivalent

Back to Top

Examples

List

In this example, a two dimensional Products List module has time on columns and line items on rows to show product categories over time.

  • The Found Product line item is list-formatted for the Products list:
  • We can then use FINDITEM to search the Products list for a product name text entered into the Product Search text-formatted line item:
FINDITEM(Products, Product Search)

  • The Found Product result will then remain blank until a text string is entered into Product Search that matches one of the Products list items:
FINDITEM(Products, Product Search)

Here, Apples and Figs are found in the Products list, but Grapefruit is not.

List Item Codes

This example is an alternative to the first example and uses code text strings to find items in the Products list.

  • Each item in the Products list has a code assigned to it:
  • We can use these code numbers entered as text into the text-formatted Product Search line item to find the same items in the list:
FINDITEM(Products, Product Search)

Note that nothing is returned for 016, because this code is not used for any of the target list items.

Number lists

Anaplan stores lists in text format, so the numbers in the list will be converted to text.

To find a number in that list, use the TEXT function to convert the number in the model to the correct format.

FINDITEM(listofnumbers, TEXT(Numbertofind))

Time

In this example we use FINDITEM with time period-formatted line items. We have a two dimensional module with versions on columns and line items on rows.

  • The Found Time Period line item is time period-formatted as Month. We can then use FINDITEM to search the month time periods list for matches for the text entered into the Find Time Text line item:
FINDITEM(Time, Find Time Text)

  • The result Found Time Period remains blank until a text string is entered into the text-formatted Find Time Text source that matches an item in the time period list:
FINDITEM(Time, Find Time Text)

Here Jan 17 is not found because it is outside the timescale of the model.

Back to Top