Range & lookup functions

This group contains 14 functions that work on ranges: extended lookups (one-way, two-way, multi-result, across every worksheet), filtering arrays and extracting unique values, summing or counting by fill colour or by visible rows, and hiding rows automatically by condition.

The examples below use a semicolon ; to separate arguments (Excel running on a Vietnamese regional format). If your Excel uses the English (US) format, type a comma , instead of ;.

Quick reference:

Function	Purpose
dvdXlookup	Extended lookup with error handling and approximate match.
dvdELookup	Return the result of the last matching row.
dvdMCLookup	Write every matching row, across several columns, into an output range.
dvdMVLookup	Return every matching value from a column offset to the lookup column.
dvdTableLookup	Two-way lookup by row header and column header.
dvdLookupAllSheets	Look a value up across all worksheets of the workbook.
dvdUnique	Return an array of the unique values of a range.
dvdUniqueV	Return the unique values, writing the remainder into an output range.
dvdUnique2DArray	Keep the unique rows of an array based on one key column.
dvdFilter2DArray	Filter a two-dimensional array on one column.
dvdSumVisible	Sum the visible cells, skipping hidden rows and columns.
dvdSumIfColor	Sum the cells whose fill colour matches a sample cell.
dvdCountIfColor	Count the cells whose fill colour matches a sample cell.
dvdAutoHide	Hide the rows that fail a condition and renumber the rest.

dvdXlookup

Looks a value up in the lookup range and returns the matching value from the result range, with optional fallbacks for "not found" and "error".

Syntax:

=dvdXlookup(LookupValue; LookupArray; ReturnArray; [IfNotFound]; [IfError]; [MatchMode])

Parameter	Required	Description
LookupValue	Yes	Value to look for inside LookupArray.
LookupArray	Yes	Range in which the search is performed.
ReturnArray	Yes	Range the result is taken from.
IfNotFound	No	Value returned when nothing matches; #N/A when omitted.
IfError	No	Value returned on error; #N/A when omitted.
MatchMode	No	0 = exact (default), -1 or 1 = approximate, like XLOOKUP.

Example:

=dvdXlookup(A6; 'Đơn giá'!$A$5:$A$500; 'Đơn giá'!$E$5:$E$500; "Chưa có đơn giá"; "Lỗi dữ liệu"; 0)
// → 1,850,000 (unit rate of the work code in A6)

=dvdXlookup(D6; $H$5:$H$20; $I$5:$I$20; ""; ""; 1)
// → the haulage factor of the nearest distance band not exceeding D6

Notes

• The function always tries an exact match first; the approximate pass only runs when nothing matched and MatchMode is not 0.
• Approximate matching works on numeric data only: -1 takes the smallest value greater than or equal to the lookup value, 1 takes the largest value less than or equal to it.

dvdELookup

Searches from the bottom up and returns the result of the last match — useful when a work code appears several times and you need the most recent record.

Syntax:

=dvdELookup(LookupValue; LookupRange; ResultRange)

Parameter	Required	Description
LookupValue	Yes	Value to look for.
LookupRange	Yes	Range containing the value to look for.
ResultRange	Yes	Range containing the result.

Example:

=dvdELookup(A6; $B$5:$B$300; $F$5:$F$300)
// → the quantity of the latest acceptance record for work code A6

Notes

• When nothing matches, the function returns the text Không tìm thấy kết quả ("no result found").
• Both ranges should have the same number of rows, because the result is taken by matching position.

dvdMCLookup

Returns every row matching the lookup value, with all of its columns, written into an output range on the worksheet.

Syntax:

=dvdMCLookup(ReturnCols; LookupColIndex; LookupValue; outputRange)

Parameter	Required	Description
ReturnCols	Yes	Columns containing the values to return.
LookupColIndex	Yes	Index of the lookup column, counted from 1 inside ReturnCols.
LookupValue	Yes	Value to look for.
outputRange	Yes	Target range where the results are written.

Example:

=dvdMCLookup($A$5:$F$500; 2; $H$3; $H$6:$M$40)
// → "Done", and every row whose item code equals H3 is written into H6:M40

Notes

• The formula cell only shows Done; the data goes into outputRange, which is cleared before writing — leave a large enough empty area.
• The function is volatile: Excel recalculates it whenever anything in the workbook changes.
• When no row matches, the function returns #N/A.

dvdMVLookup

Returns every matching value from a column offset to the lookup column: the first hit appears in the formula cell, the remaining hits are written into the output range.

Syntax:

=dvdMVLookup(LookupValue; LookupCol; OffsetCol; outputRange)

Parameter	Required	Description
LookupValue	Yes	Value to look for.
LookupCol	Yes	Column containing the value to look for.
OffsetCol	Yes	Number of columns to the right of LookupCol holding the returned value.
outputRange	Yes	Target range for the second and later values (for Excel 2019/2016 without dynamic arrays).

Example:

=dvdMVLookup($H$3; $B$5:$B$500; 4; $J$4:$J$40)
// → the first quantity for work code H3; the remaining quantities go into J4:J40

Notes

• The output range is cleared before writing; pick an empty area outside the data table.
• The function is volatile: Excel recalculates it whenever anything in the workbook changes.

dvdTableLookup

Two-way lookup: returns the value at the intersection of the row and the column identified by their headers.

Syntax:

=dvdTableLookup(LookupRowValue; LookupColumnValue; TableRange)

Parameter	Required	Description
LookupRowValue	Yes	Value to find in the first column of the table.
LookupColumnValue	Yes	Value to find in the first row of the table.
TableRange	Yes	Data table, including both row and column headers.

Example:

=dvdTableLookup("D16"; "Cấp bền B22.5"; $A$5:$H$30)
// → 1.284 (norm looked up by rebar diameter and concrete grade)

Notes

• When the row or the column header is not found, the function returns the text Không tìm thấy kết quả ("no result found").

dvdLookupAllSheets

Looks a value up across every visible worksheet of the workbook and returns the value from the requested column.

Syntax:

=dvdLookupAllSheets(lookupValue; lookup_column; result_column)

Parameter	Required	Description
lookupValue	Yes	Value to look for.
lookup_column	Yes	Number of the column containing the value to look for (column A is 1).
result_column	Yes	Number of the column containing the value to return.

Example:

=dvdLookupAllSheets(A6; 2; 6)
// → the quantity in column F of the row whose work code is A6, searched on every quantity sheet

Notes

• The worksheet holding the formula and any hidden worksheets are skipped; when nothing matches the function returns #N/A.
• Columns are counted as worksheet columns, not as positions inside a range.

dvdUnique

Returns a one-column array holding the unique values of a range.

Syntax:

=dvdUnique(rng)

Parameter	Required	Description
rng	Yes	Input data range.

Example:

=dvdUnique($B$6:$B$500)
// → the list of distinct items: Móng, Cột, Dầm, Sàn

Notes

• The function returns an array: Excel 365/2021 spills it automatically; on Excel 2019 and earlier select the target range and press Ctrl+Shift+Enter, or use dvdUniqueV.
• Values are compared case-insensitively.

dvdUniqueV

Returns the first unique value in the formula cell and writes the remaining unique values into the output range — made for Excel versions without dynamic arrays.

Syntax:

=dvdUniqueV(inputRange; outputRange)

Parameter	Required	Description
inputRange	Yes	Range holding the values to deduplicate.
outputRange	Yes	Range where the remaining unique values are written (for Excel 2019/2016).

Example:

=dvdUniqueV($B$6:$B$500; $H$7:$H$40)
// → "Móng" in the formula cell; the other items are written into H7:H40

Notes

• The output range is cleared before writing.
• The function is volatile: Excel recalculates it whenever anything in the workbook changes.
• Empty cells in the source range are skipped.

dvdUnique2DArray

Keeps the unique rows of a two-dimensional array based on one key column.

Syntax:

=dvdUnique2DArray(sArray; colIndex; HasTitle)

Parameter	Required	Description
sArray	Yes	Input two-dimensional array.
colIndex	Yes	Index of the key column, counted from 1.
HasTitle	Yes	TRUE when the data has a header row.

Example:

=dvdUnique2DArray($A$5:$F$500; 2; TRUE)
// → the subcontractor table with only the first occurrence of each code

Notes

• The function returns a two-dimensional array: Excel 365/2021 spills it; older versions need it entered as an array formula with Ctrl+Shift+Enter.
• With HasTitle set to TRUE the header row is always kept at the top of the result.

dvdFilter2DArray

Filters a two-dimensional array on a condition applied to one column and returns the matching rows.

Syntax:

=dvdFilter2DArray(DataArray; ColumnIndex; Criteria; HasTitle)

Parameter	Required	Description
DataArray	Yes	Two-dimensional array holding the data to filter.
ColumnIndex	Yes	Index of the column the criteria apply to, counted from 1.
Criteria	Yes	Filter criteria, for example ">5", "abc", "abc*".
HasTitle	Yes	TRUE when the data has a header row.

Example:

=dvdFilter2DArray($A$5:$F$500; 6; ">0"; TRUE)
// → the table reduced to work items with a quantity greater than zero

=dvdFilter2DArray($A$5:$F$500; 2; "BT*"; TRUE)
// → the rows whose work code starts with "BT"

Notes

• The function returns a two-dimensional array: Excel 365/2021 spills it; older versions need it entered as an array formula with Ctrl+Shift+Enter.
• Criteria accept comparison operators (">5", ">=2", "<>0") and wildcards (*, ?).

dvdSumVisible

Sums the values of the visible cells, skipping any cell in a hidden row or column.

Syntax:

=dvdSumVisible(SumRng)

Parameter	Required	Description
SumRng	Yes	Range whose visible cells are summed.

Example:

=dvdSumVisible($F$6:$F$500)
// → the total amount of the rows still visible after filtering by item

Notes

• The function is volatile: Excel recalculates it whenever anything changes, so avoid using it hundreds of times on a very large sheet.
• Cells hidden by AutoFilter, hidden manually or hidden by dvdAutoHide are all skipped.

dvdSumIfColor

Sums the cells whose fill colour matches the fill colour of a sample cell.

Syntax:

=dvdSumIfColor(rngSum; rngCellColor)

Parameter	Required	Description
rngSum	Yes	Range whose values are summed.
rngCellColor	Yes	Cell whose fill colour is used as the condition.

Example:

=dvdSumIfColor($F$6:$F$500; $H$3)
// → the total quantity of the rows filled with the same colour as H3 (for example the "variation" mark)

Notes

• Changing a fill colour does not trigger a recalculation; press Ctrl+Alt+F9 after recolouring to refresh the result.
• The comparison uses the fill colour applied directly to the cell, not colours produced by conditional formatting.

dvdCountIfColor

Counts the cells in a range whose fill colour matches the fill colour of a sample cell.

Syntax:

=dvdCountIfColor(RangeToCount; ReferenceCell)

Parameter	Required	Description
RangeToCount	Yes	Range of cells to count.
ReferenceCell	Yes	Cell whose fill colour is used as the reference.

Example:

=dvdCountIfColor($B$6:$B$500; $H$4)
// → 12 (number of items flagged as behind schedule)

Notes

• Changing a fill colour does not trigger a recalculation; press Ctrl+Alt+F9 after recolouring to refresh the result.
• The comparison uses the fill colour applied directly to the cell, not colours produced by conditional formatting.

dvdAutoHide

Hides the rows that fail the condition and renumbers the rows that stay visible.

Syntax:

=dvdAutoHide(Target; Cells; Conditions; [OrdersRange]; [Title])

Parameter	Required	Description
Target	Yes	Range of rows covered by the hide/show operation.
Cells	Yes	Range holding the values compared against the conditions.
Conditions	Yes	Condition, for example "0", ">=2", "<>0"; several conditions can be supplied as a range.
OrdersRange	No	Range that is renumbered after hiding.
Title	No	Text shown in the formula cell, DVDAutoHide by default.

Example:

=dvdAutoHide($A$6:$A$500; $F$6:$F$500; "<>0"; $A$6:$A$500; "Ẩn dòng khối lượng 0")
// → keeps the rows with a non-zero quantity, hides the rest and renumbers them consecutively

Notes

• A row stays visible when at least one of its cells in Cells satisfies every condition; all other rows are hidden.
• Hiding runs after Excel finishes calculating, so the result may appear one beat later than the formula entry.
• The function is volatile: Excel recalculates it whenever anything in the workbook changes.
• Put the formula in a cell outside Target so that it is not hidden along with the rows.