In Excel 2021 and 365, the CHOOSEROWS + FILTER functions are an efficient way to retrieve multiple matches.
=CHOOSEROWS(
FILTER(
TableName[CategoryToReturn],
TableName[CategoryToFilter]="Criteria"
),
nth
)
If you want this to mimic vlookup, then you would simply use 1 as the nth item:
=CHOOSEROWS(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),1)
But you could also look up the LeadTech for the 2nd WorkOrder that's labelled "Replace":
=CHOOSEROWS(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),2)
Or you can also return more than one row, say the 2nd and 4th rows:
=CHOOSEROWS(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),2,4)
Finding the nth last item is also now simple, because CHOOSEROWS accepts negative values (yes!!):
=CHOOSEROWS(
FILTER(
TableName[CategoryToReturn],
TableName[HeadingName]="Criteria"
),
-nth
)
For instance, this formula looks up the Lead Tech for the 2nd last WorkOrder that's labelled "Replace":
=CHOOSEROWS(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),-2)
To return the first x items, you can use the TAKE function instead of CHOOSEROWS.
=TAKE(
FILTER(
TableName[CategoryToReturn],
TableName[CategoryToFilter]="Criteria"
),
NumberOfRows
)
For instance, this formula looks up Lead Techs for the first two WorkOrders that are labelled "Replace":
=TAKE(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),2)
And the LeadTechs for the last two WorkOrders:
=TAKE(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),-2)
To exclude the first x items, you can use the DROP function instead of CHOOSEROWS.
=DROP(
FILTER(
TableName[CategoryToReturn],
TableName[CategoryToFilter]="Criteria"
),
NumberOfRows
)
For instance, this formula looks up Lead Techs for all but the first two WorkOrders that are labelled "Replace":
=DROP(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),2)
And the LeadTechs for all but the last two WorkOrders:
=DROP(FILTER(WorkOrders[LeadTech],WorkOrders[Service]="Replace"),-2)
An array formula can be used to find the nth match (Use CTRL-SHIFT-ENTER to make it an array formula):
{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}
See https://exceljet.net/formula/get-nth-match-with-index-match for more details.
I suppose if you want to find the nth last match then you can just use "LARGE" instead of small:
{=INDEX(array,LARGE(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}
To return the last value instead of the first value, you can use a lookup formula hack.
If your lookup value is in C1, use:
=LOOKUP(2,1/(A1:A10=C1),B1:B10)
The condition (A1:A10=C1) returns, after coercing, 1 for match and 0 for no match
E.g. an array like:
{0;0;0;1;0;0;1;0}
If you divide 1 by that array, 1/(A1:A10=C1) it will look like:
{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}
A lookup with LOOKUP formula and a lookup value greater than any value in the range will return the last numerical value i.e. the last 1, representing the last match.
See http://www.mrexcel.com/forum/excel-questions/201255-vlookup-last-match.html for more details.
Older versions do not have array formulas and do not have LARGE or SMALL. So a User Defined Function is the only way.
With a little help from: http://www.ozgrid.com/Excel/find-nth.htm
Here's a function that will find the nth item, and the nth last item, and perform a lookup across to another column/row.
The custom functions can be used like shown below
=Nth_Occurrence($B$1:$B$22,"Harry",3,0,1)
=Nth_Last_Occurrence($B$1:$B$22,"Harry",3,0,1)
The syntax is
=Nth_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)
=Nth_Last_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)
Where $B$1:$B$22 (range_look) is the range to find the 3rd occurrence (occurrence) or 3rd last occurrence of "Harry" (find_it). When found, it will return the value by offsetting 0 rows (offset_row) and 1 column(offset_col) to the right. The offset_row and offset_col arguments can be negative values if that is what is needed.
Public Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
If find_it = "" Then
Nth_Occurrence = ""
Exit Function
End If
TotalCount = Application.WorksheetFunction.CountIf(range_look, find_it)
If TotalCount - occurrence + 1 <= 0 Then
Nth_Occurrence = "N/A"
Exit Function
End If
For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
Public Function Nth_Last_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim TotalCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
' Find number of hits
If find_it = "" Then
Nth_Last_Occurrence = ""
Exit Function
End If
TotalCount = Application.WorksheetFunction.CountIf(range_look, find_it)
If TotalCount - occurrence + 1 <= 0 Then
Nth_Last_Occurrence = "N/A"
Exit Function
End If
' Find nth last hit
For lCount = 1 To TotalCount - occurrence + 1
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Last_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
Sub DescribeFunction()
' Run this function once to add it to the function register
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 5) As String
FuncName = "Nth_Occurrence"
FuncDesc = "Returns the nth occurrence of a string in a range"
Category = 7 'Text category
ArgDesc(1) = "Range you're looking in"
ArgDesc(2) = "String you're looking for"
ArgDesc(3) = "Number of occurrence to return"
ArgDesc(4) = "Row offset of cell to return"
ArgDesc(5) = "Column offset of cell to return"
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
FuncName = "Nth_Last_Occurrence"
FuncDesc = "Returns the nth last occurrence of a string in a range"
Category = 7 'Text category
ArgDesc(1) = "Range you're looking in"
ArgDesc(2) = "String you're looking for"
ArgDesc(3) = "Number of nth last occurrence to return"
ArgDesc(4) = "Row offset of cell to return"
ArgDesc(5) = "Column offset of cell to return"
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub