Locates a specific row in a table and the returns the value for a specific column. The row can be identified using a combination of a filter and an offset. First the filter is applied to the rows in the table to find matching rows. The offset is used to identify the row number to use.
This function can be added to your worksheet by using the Function Wizard.
Syntax
DBFind( Connection , Table , Column , filter , offset , [ parameter1 ] , [ ...parameter n ] )
The DBFind function syntax has these parts:
| Value | Description |
|---|---|
| connection | Required. This argument identifies the connection name used to connect to the database. |
| table | Required. This argument identifies an SQL statement or a specific internal table name in the database. If this argument does not supply a valid source of data, then the result of the function will be #TABLE? ("table name" not found). |
| column | Required. This argument identifies an internal field name or alias returned by the table name argument. If this argument does not contain a valid field name, then the result of the function will be #FIELD? ("field name" not found). |
| filter | Optional. A filter is a string of text that includes comparisons between fields in the table and specific values as a literal value or a parameter. Parameters to a cell reference are indicated by using a question mark placeholder within the filter string. The function will evaluate the question mark placeholder with the corresponding parameter. Only the records that satisfy the filter conditions will be used to return values. |
| Offset | Required. If more than one record meets the filter conditions, this value may be used to indicate which record the value should be read from. A value of 0 returns the first match. Positive values indicate the number of records that should be skipped (e.g. 1 would skip the first match and return the second match). The value for offset must be in the range of -n to n-1, where n represents the number of records satisfying the filter. If the value for offset is outside of this range, no value is returned. |
| parameter 1 , [ ...parameter n ] | Required if filter uses placeholders. This is a comma-delimited list of values or cell references used to replace the corresponding question mark placeholders within the filter string. Parameter 1 is used for the first question mark placeholder in the filter, Parameter 2 would represent the second question mark placeholder, and so on. |
DBFind Example
Returns the first vendor name found based on the condition of the vendor number in cell A5.
=DBFind("Sage 100 Contractor", "actpay", "vndnme", "recnum=?", "First", A5)
For a video demonstration of implementing this function, check our
Recorded Training
Remarks
If the function does not return a value, Excel may interpret that to be a zero or a date depending on the cell format.