[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Grid View
  [Expand]Tree List
  [Expand]Card View
  [Expand]Chart Control
  [Expand]Pivot Grid
   [Expand]Product Information
    Getting Started
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Expand]Cell Basics
    [Collapse]Spreadsheet Formulas
       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)
      Array Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
    [Expand]Mail Merge
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Site Navigation and Layout
  [Expand]HTML Editor
  [Expand]Vertical Grid
  [Expand]Data Editors
  [Expand]Docking and Popups
  [Expand]File Management
  [Expand]Data and Image Navigation
  [Expand]Multi-Use Site Controls
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]CodeRush Classic
[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 ASPxSpreadsheet.




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] )

Is this topic helpful?​​​​​​​