SoftArtisans ExcelWriter Guide

All Functions Math Functions String Functions Logical Functions
Time Functions Statistical Functions Information Functions
Lookup & Reference Functions Database Functions Financial Functions

Lookup and Reference Functions

The only functions available in
ExcelWriterSE and ExcelWriterLE
are sum and average.

Column*
Columns*
Hlookup*
Row*
Rows*
Vlookup*

Logical Functions

Column*

Returns the column number of a specified cell, or the array of column numbers of a specified range of cells. If you do not specify a cell, Column will return the column number of the cell containing the Column formula.

Formula in cell g2	Value displayed in g2
ws.cells("g2").formula = "=column(f3)"	6
ws.cells("g2").formula = "=column()"	7
ws.cells("g2").formula = "=column(a1:d6)"	{1;2;3;4}

Columns*

Returns the number of columns in a specified range of cells. For example, cells a1:e1 span 5 rows, so the following will assign the value 5 to cell g7.

 
	ws.cells("g7").formula = "=columns(a1:e1)"

Hlookup*

Returns a value in a specified table column, based on the value in the first row of the column (presumably, the column name), and a row number. Hlookup takes four arguments:

lookup_value	The comparison value in the first row of the table (in most cases, the column name). Use either a cell reference, or a cell value.
table_array	A range of cells representing the table being searched.
row_index_num	Number of the row to search.
[range_lookup]	If True, Hlookup will search against the next largest value that is smaller than lookup_value. If False, Hlookup will return #N/A if it cannot find lookup_value. Optional. Default value = True.

Use Hlookup, when the comparison value is in the first row of the table (that is, when the comparison value is the first value in a column), and Vlookup when the comparison value is in the first column of the table.

Example: If cells G7:G10 contain, "Last Name", "Brown", "Green", and "Silver", cells H7:H10 contain "First Name", "Helen", "James", and "Louise", and cells I7:I10 contain "Salary", 60000, 58000, 52000, the following will return 58000, which is the value in the third row of the "Salary" column.

 
	=Hlookup("Salary",G7:I10,3)

Row*

Returns the row number of a specified cell, or the array of row numbers of a specified range of cells. If you do not specify a cell, Row will return the row number of the cell containing the Row formula.

Formula in cell g2	Value displayed in g2
ws.cells("g2").formula = "=row(f3)"	3
ws.cells("g2").formula = "=row()"	2
ws.cells("g2").formula = "=row(a1:a5)"	{1;2;3;4;5}

Rows*

Returns the number of rows in a specified range of cells. For example, cells a1:a4 span 4 rows, so the following will assign the value 4 to cell g7.

 
	ws.cells("g7").formula = "=rows(a1:a4)"

Vlookup*

Returns a value in a specified table row, based on the value in the first column of the row, and a column number. Vlookup takes four arguments:

lookup_value	The comparison value in the first column of the table. Use either a cell reference, or a cell value.
table_array	A range of cells representing the table being searched.
row_index_num	Number of the column to search.
[range_lookup]	If True, Vlookup will search against the next largest value that is smaller than lookup_value. If False, Vlookup will return #N/A if it cannot find lookup_value. Optional. Default value = True.

Use Vlookup when the comparison value is in the first column of the table (that is, when the comparison value is the first value in a row). Use Hlookup when the comparison value is in the first row of the table.

Example: If cells G7:G10 contain, "Last Name", "Brown", "Green", and "Silver", cells H7:H10 contain "First Name", "Helen", "James", and "Louise", and cells I7:I10 contain "Salary", 60000, 58000, 52000, the following will return 60000, which is the value in the third column of Helen Brown's row.

 
	=Vlookup("Brown",G7:I10,3)

*This function is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. The only functions available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree are sum and average.