AVLOOKUP2 Examples

These examples can be found in the Examples subfolder of the FastExcel V4 SpeedTools install directory.

Approx Lookup, ascending, exact result

These examples look for “Superphone” in column C and return the corresponding value from column E.

Approx match returns the last result that matches.
VLOOKUP(“superphone”,C2:E7,3)=1200
AVLOOKUP2(“superphone”,c2:e7,3,true)=1200
AVLOOKUP2(“superphone”,c1:e7,”spend”,”asc”)=1200

You can use the function wizard to help with the function arguments: 

And then scroll down for the remaining arguments:

Approx lookup, ascending, approx result

These examples look for “spacetime” in column C, but there is no “spacetime” so they find the row with the largest value less than “spacetime” and return the corresponding value from column E.

“spacetime” falls between “SpaceCalls” and “Superphone”, so row 3 is the largest value less than “spacetime”.
VLOOKUP(“spacetime”,c2:e7,3) =205

The default option for AVLOOKUP2 is to do an exact match even with sorted data, so #N/A is returned
AVLOOKUP2(“spacetime”,c1:e7,”spend”,1) = #N/A

You can make AVLOOKUP2 do an approximate match by giving FALSE as the 5th argument.
AVLOOKUP2(“spacetime”,c1:e7,”spend”,1,FALSE) =205

Exact match, not sorted, exact result

These examples look for “superphone” in column C and return the corresponding value from column C. Using AVLOOKUP2 you can either use the column number or the label of the column (“Spend”). Using the label gives you much better protection when someone inserts a column between Users and Spend.

Exact match returns the first result that exactly matches.
VLOOKUP(“superphone”,c2:e7,3,false) =100
AVLOOKUP2(“superphone”,c1:e7,”spend”,”No”) =100

Exact Match, sorted, not found

This example looks for an exact match to “spacetime” in column C, and cannot find one.

The default value returned if an exact match is not found is #N/A.
VLOOKUP(“spacetime”,c2:e7,3,false) = #N/A

The Sorted parameter is “No” so AVLOOKUP does an exact match and returns the default error #N/A.
AVLOOKUP2(“Spacetime”,C1:E7,”spend”,”No”) = #N/A

AVLOOKUP2 can also do an exact match lookup on sorted data, and it is much faster than an exact match on unsorted data, particularly with large ranges.
AVLOOKUP2(“Spacetime”,C1:E7,”spend”,”ASC”,True) = #N/A

Getting rid of the #N/A

If you want to return a value other than #N/A you can use IF tests with either COUNTIF or a double lookup.
IF(COUNTIF(C2:C7,”=spacetime”)=0,”None”, VLOOKUP(“Spacetime”,c2:e7,3,True)) = None

Using AVLOOKUP2 is simpler and faster because it allows you to set the error return value directly and can take advantage of sorted data.
AVLOOKUP2(“Spacetime”,c1:e7,”spend”,”asc”,”None”) = None

Lookup column not the first column

This example looks for “Superphone” in the “Product” column and returns the corresponding value from the “Spend” column.

To lookup a column that is not the first column you can use INDEX and MATCH.
INDEX(A2:E7,MATCH(“Superphone”,c2:c7,1),5) = 1200

With AVLOOKUP2 you can specify both the return column and the lookup column directly.
AVLOOKUP2("superphone",A1:E7,"spend",TRUE,FALSE,"product") = 1200

Two-dimensional lookup

This example looks for “Superphone” in column C and “Spend” in row 1, and returns the value of the intersection of the row and column found.

You can do a two-dimensional lookup using INDEX with two MATCH functions.
INDEX(A2:E7,MATCH(“Superphone”,c2:c7,1),MATCH(“Spend”,A1:E1,0)) =1200

AVLOOKUP2 handles two-dimensional lookups directly.
AVLOOKUP2(“Superphone”,A1:E7,”Spend”,True,False,”product”) = 1200

Error handling two-dimensional lookups using INDEX and MATCH is messy.
IF(ISNA(INDEX(A2:E7,MATCH(“superphone”,C2:C7,1), MATCH(“spend”,A1:E1,0))), ”None”,INDEX(A2:E7,MATCH(“superphone”,C2:C7,1), MATCH(“spend”,A1:E1,0))) = 1200

AVLOOKUP2 handles error handling for two-dimensional lookups directly and much more efficiently.
AVLOOKUP2(“Superphone”,A1:E7,”spend”,”Asc”,”None”,”product”) = 1200

Multicolumn and 2D lookup

This example looks for a row with “superphone” in the “product” column and “defense” in the “sector” column, and returns the value from the “Users” column. If a row that meets these conditions is not found, AVLOOKUP2 returns “None”.

Looking up multiple columns is simple and efficient with AVLOOKUP2.
AVLOOKUP2({“superphone”,”defense”},A1:E7,”Users”,”Asc”,”None”,{“product”,”sector”}) = 5
The two values to look up and the two columns to look up are specified as arrays using curly brackets. You should NOT enter this formula as an array formula.

Often it is better to reference ranges that contain the values rather than use arrays. This example assumes that G41:H41 contains “superphone” and “defense”, and I41:J41 contains “Product” and “Sector”.
AVLOOKUP2($G$41:$H$41,$A$1:$E$7,"users","ascending","None",$I$41:$J$41)=5

When using ranges containing the lookup values in this way the data needs to be in adjacent columns

Multicolumn lookup with columns that are not adjacent

When the columns containing the lookup values that you want to lookup are not next to one another you can use the COL.ARRAY function to create an array that makes the data adjacent.

So if you want to look up spend for a Sector and Channel the function would look like this:
AVLOOKUP2(COL.ARRAY(,C51,A51),A1:E7,"Spend","Asc","None",{"Sector","Channel"})
(note the , at the start of the COL.ARRAY function argument list: this is to use trhe default PAD argument)

Using Ranges, 6-cell array formula in D50:E52

This is an example of using AVLOOKUP2 as a multi-cell array formula. The formula is entered into D50:E52 as an array formula using Ctrl-Shift-Enter, and returns six results because there are three rows to lookup (50:52, each row has 3 columns to lookup) and two columns (D:E) to return from.

The example looks up “direct” “superphone” and “defense” in the columns labelled “Channel” “product” and “sector” and returns the values from the corresponding row in columns “Spend” and “Users” (150 and 5). It then looks up “direct” “superphone” and “mfg” and returns the values 1200 and 80, and finally looks up “retail” “spacecalls” and “mfg” to return the error values “none” and “none”.
{AVLOOKUP2(A50:C52,A1:E7,D49:E49,"Not","none",A49:C49)}
This multi-cell array formula is the equivalent of these six individual formulas:
AVLOOKUP2(A50:C50,A1:E7,D49,"Not","none",A49:C49)
AVLOOKUP2(A50:C50,A1:E7,E49,"Not","none",A49:C49)
AVLOOKUP2(A51:C51,A1:E7,D49,"Not","none",A49:C49)
AVLOOKUP2(A51:C51,A1:E7,E49,"Not","none",A49:C49)
AVLOOKUP2(A52:C52,A1:E7,D49,"Not","none",A49:C49)
AVLOOKUP2(A52:C52,A1:E7,E49,"Not","none",A49:C49)

Note that the sequence of the column names in the Lookup is not the same as the sequence of the column names in the data.

Returning multiple rows and columns using arrays and a 4-cell array formula

You can use arrays of constants or ranges in AVLOOKUP2. In this example the formula is Ctrl-shift-entered into A55:B56.

The first array of lookup values has two rows each containing two lookup values: (comma separates columns, semicolon separates rows).
The second array {spend, users} specifies the names of the two columns to return values from.
The third array {Product, sector} specifies the names of the two columns to look up.

{AVLOOKUP2({"Superphone","defense";"superphone","mfg"},A1:E7,{"spend","users"},"Asc","None",
{"product","sector"})}

The results ofthe array formula are shown below:

Using SUM and wildcards with AVLOOKUPS2

This example uses AVLOOKUPS2 to return all the rows that match a simple lookup for Channel and Sector and a wildcard lookup for Product. The results are summed separately for Spend and for Users. If no matching rows are found then 0 will be returned.

D59= SUM(AVLOOKUPS2($A59:$C59,$A$1:$E$7,D$49,"Not",0,$A$49:$C$49))

Fill across to E59 and down to D60:E60

This is NOT an array formula and does not need to be entered using Ctrl-shift-Enter.

The answer value of 1405 in D59 is the sum of the Spend column for all the rows that have Channel=Direct and Sector=Mfg and Product contains a P.

The result of 1405 in D59 comes from the rows returned to SUM as follows:

Row 3: Direct Mfg SpaceCalls = 205

Row 7: Direct Mfg SuperPhone = 1200

The other results are obtained in a similar way.

Copyright © 2023 Decision Models Ltd