Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Spreadsheet Formulas
    [Collapse]Functions
      Mathematical Functions
      Statistical Functions
      Date and Time Functions
      Text Functions
      Financial Functions
      Logical Functions
      Lookup and Reference Functions
      Engineering Functions
      Information Functions
      Compatibility Functions
      Database Functions
      Web Functions
      User-Defined Functions (UDF)
      Real Time Data (RTD) function
     Operators
     Array Formulas
     Calculation
     Formula Engine
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Lookup and Reference Functions

This document briefly describes lookup and reference functions implemented in the XtraSpreadsheet.

Name

Description

Syntax

ADDRESSReturns a text reference for a specified row and column number.ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
AREASReturns the number of areas in a range.AREAS(reference)
CHOOSESelects a value from a list based on its index number.CHOOSE(index_num, value1, [value2], ...)
COLUMNReturns the first column number within the cell reference or the number of the current column if no reference is supplied.COLUMN([reference])
COLUMNSReturns the number of columns in an array or reference.COLUMNS(array)
FORMULATEXTReturns what is displayed in the formula bar if you select the referenced cell.FORMULATEXT(reference)
HLOOKUPLooks up a value in the first table row, and returns a value in the same column from another row.HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
HYPERLINKCreates a hyperlink.HYPERLINK(link_location,friendly_name)
INDEXReturns the value of an element in a table or an array, selected by the row and column number indexes.INDEX(array, row_num, [column_num])
INDIRECTReturns the reference specified by a text string.INDIRECT(ref_text, [a1])
LOOKUPReturns a value from a cell in a position found by lookup in a search table. Vector form: LOOKUP(lookup_value, lookup_vector, result_vector )
'lookup_value' is the value to look up in the 'lookup_vector' single column (single row) range, 'lookup_vector' is a list of data (single column or row range) used to search for the lookup_value; 'result_vector' is a range of the same size as 'lookup_vector'. The function returns the value in 'result_vector' at the position where the match is found in 'lookup_vector'.
Array form: LOOKUP(lookup_value, array)
'lookup_value' is the value that you wish to look up in the specified array and 'array' is a two-dimensional array of data. The first column (or row) of an array will be used to search for the 'lookup_value', and the value in the corresponding last column (or row) will be returned.
MATCHSearches for a specified item in a range of cells, and returns the relative position of that item in the range.MATCH(lookup_value, lookup_array, [match_type])
OFFSETReturns a reference to a range that is located a specified number of rows and columns away from a cell or range of cells.OFFSET(reference, rows, cols, [height], [width])
ROWReturns the first row number within the cell reference or the number of the current row if no reference is supplied.ROW([reference])
ROWSReturns the number of rows in an array or reference.ROWS(array)
TRANSPOSETransforms a horizontal range of cells into a vertical range, and vice versa.TRANSPOSE(array)
VLOOKUPLooks up a value in the first column of a table, and returns a value in the same row from another column.VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

How would you rate this topic?​​​​​​​