Fonctions Recherche & Ref

[ ] indicates optional parameters

ADRESSE(no_lig; no_col; [no_abs]; [a1]; [feuille_texte])
CHOISIR(no_index; valeur1; [valeur2; ...])
COLONNE([référence])
COLONNES(plage)
DECALER(référence; lignes, colonnes; [hauteur]; [largeur])
EQUIV(valeur_cherchée; tableau_recherche; [type])
INDEX(référence; [no_lig]; [no_col]; [no_zone])
INDIRECT(réf_texte; [a1])
LIEN_HYPERTEXTE(emplacement_lien; [nom_convivial])
LIGNE([référence])
LIGNES(plage)
RECHERCHE(valeur_cherchée; vecteur_recherche; [vecteur_résultat])
RECHERCHEH(valeur_cherchée; tableau; no_index_lig; [valeur_proche])
RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche])
TRANSPOSE(plage)
ZONES(référence)


Fonctions Recherche & Ref

ADRESSE(no_lig; no_col; [no_abs]; [a1]; [feuille_texte])

Returns a string containing the specified cell address.

no_ligThe row number of the cell.
no_colThe column number of the cell.
no_absA number representing whether the row or column are to be absolute or relative. The possible values are:
1Both absolute
2Absolute row, relative column
3Relative row, absolute column
4Both relative
If this parameter is omitted it defaults to 1.
a1Specifies the style of the reference. The possible values are:
FAUXR1C1 style
VRAIA1 style
If this parameter is omitted it defaults to VRAI.
feuille_texteOptional sheet name with which to prefix the reference.


CHOISIR(no_index; valeur1; [valeur2; ...])

Returns one of several values depending on the index.

no_indexThe index of the value to be returned, should be in the range 1 to 29.
valeur1; ...Up to 29 values, one of which will be chosen to be the result.


COLONNE([référence])

Returns the column number of the reference.

référenceThe reference whose column number you want. If this parameter is omitted it defaults to the cell containing the function.


COLONNES(plage)

Returns the number of columns in the reference.

plageThe reference whose columns you want to count.


DECALER(référence; lignes; colonnes; [hauteur]; [largeur])

Returns a new reference based on the specified reference.

référenceThe reference to be used as a starting point.
lignesThe number of rows to move the reference up (negative) or down (positive).
colonnesThe number of columns to move the reference left (negative) or right (positive).
hauteurThe height of the new reference. If this parameter is omitted it defaults to the height of the old reference.
largeurThe width of the new reference. If this parameter is omitted it defaults to the width of the old reference.


EQUIV(valeur_cherchée; tableau_recherche; [type])

Returns a number representing the position of a value in a table.

valeur_cherchéeThe value to be found in the table.
tableau_rechercheA reference containing the table cells.
typeWhether to find an approximate or exact match. The possible values are:
1If an exact match is not found, use the closest value less than the lookup value (the table values should be in ascending order)
0An exact match is required
-1If an exact match is not found, use the closest value greater than the lookup value (the table values should be in descending order)
If this parameter is omitted it defaults to 1.


INDEX(référence; [no_lig]; [no_col]; [no_zone])

Returns a subset of an array or reference.

référenceThe array or reference of which you want the subset.
no_ligThe number of the row to return. If this parameter is omitted all rows will be returned.
no_colThe number of the column to return. If this parameter is omitted all columns will be returned.
no_zoneThe number of the area to return when the reference contains more than one area. If this parameter is omitted it defaults to 1.


INDIRECT(réf_texte; [a1])

Returns a reference from the specified text.

réf_texteA text expression that evaluates to the name of a cell or range of cells.
a1Specifies the style of the reference. The possible values are:
FAUXR1C1 style
VRAIA1 style
If this parameter is omitted it defaults to VRAI.


LIEN_HYPERTEXTE(emplacement_lien; [nom_convivial])

Jumps to a cell or range when this cell is selected.

emplacement_lienA text expression that evaluates to the form "filename" or "[filename]reference".
nom_convivialThe text to be displayed in the cell. If this parameter is omitted it defaults to the link location text.


LIGNE([référence])

Returns the row number of the reference.

référenceThe reference whose row number you want. If this parameter is omitted it defaults to the cell containing the function.


LIGNES(plage)

Returns the number of rows in the reference.

plageThe reference whose rows you want to count.


RECHERCHE(valeur_cherchée; vecteur_recherche; [vecteur_résultat])

Returns a value from a horizontal or vertical table, found by searching for the lookup value in the top row (for a horizontal table) or left column (for a vertical or square table) of the table and then returning a value from the corresponding position in the result range (if specified) or from the bottom row (for a horizontal table) or right column (for a vertical or square table) of the table.

valeur_cherchéeThe value to be found in the table.
vecteur_rechercheA reference containing the table cells.
vecteur_résultatThe range of cells from which to return a result. If this parameter is not specified the result will be returned from the opposite row or column of the table range.


RECHERCHEH(valeur_cherchée; tableau; no_index_lig; [valeur_proche])

Returns a value from a horizontal table, found by searching for the lookup value in the top row of the table and then returning a value from the same or a different row in the table.

valeur_cherchéeThe value to be found in the table.
tableauA reference containing the table cells.
no_index_ligThe offset of the value to be returned, where 1 is the top row of the table.
valeur_procheWhether to find an approximate or exact match. The possible values are:
FAUXAn exact match is required
VRAIIf an exact match is not found, use the closest value less than the lookup value
If this parameter is omitted it defaults to VRAI.


RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; [valeur_proche])

Returns a value from a vertical table, found by searching for the lookup value in the left column of the table and then returning a value from the same or a different column in the table.

valeur_cherchéeThe value to be found in the table.
table_matriceA reference containing the table cells.
no_index_colThe offset of the value to be returned, where 1 is the left column of the table.
valeur_procheWhether to find an approximate or exact match. The possible values are:
FAUXAn exact match is required
VRAIIf an exact match is not found, use the closest value less than the lookup value
If this parameter is omitted it defaults to VRAI.


TRANSPOSE(plage)

Returns the transposition of the specified array or reference.

plageThe array or reference whose values you want to transpose.


ZONES(référence)

Returns the number of areas contained in the reference.

referenceThe reference whose areas you want to count.