Lookup Formulas

Find a matching valueLookup - Find
Find a matching value case sensitiveLookup - Find
Find a matching value cross referenceLookup - Find
Find a matching value in any columnLookup - Find
Find a matching value in first columnLookup - Find
Find a partial matchLookup - Find
Find a particular occurrenceLookup - Find
Find the closest matchLookup - Find
Find the largest numberLookup - Find
Find the largest number excluding zeroLookup - Find
Find the largest number with 1 conditionLookup - Find
Find the largest number with multiple conditionsLookup - Find
Find the last entry in a columnLookup - Find
Find the n largest numbersLookup - Find
Find the n smallest numbersLookup - Find
Find the nth largest numberLookup - Find
Find the nth largest number with 1 conditionLookup - Find
Find the nth matchLookup - Find
Find the nth smallest numberLookup - Find
Find the nth smallest number with 1 conditionLookup - Find
Find the smallest numberLookup - Find
Find the smallest number excluding zeroLookup - Find
Find the smallest number with 1 conditionLookup - Find
Find the smallest number with multiple conditionsLookup - Find
Find the unique values in a rangeLookup - Find
Find a matching value
Lookup - Find
Find a matching value case sensitive
Lookup - Find
Find a matching value cross reference
Lookup - Find
Find a matching value in any column
Lookup - Find
Find a matching value in first column
Lookup - Find
Find a partial match
Lookup - Find
Find a particular occurrence
Lookup - Find
Find the closest match
Lookup - Find
Find the largest number
Lookup - Find
Find the largest number excluding zero
Lookup - Find
Find the largest number with 1 condition
Lookup - Find
Find the largest number with multiple conditions
Lookup - Find
Find the last entry in a column
Lookup - Find
Find the n largest numbers
Lookup - Find
Find the n smallest numbers
Lookup - Find
Find the nth largest number
Lookup - Find
Find the nth largest number with 1 condition
Lookup - Find
Find the nth match
Lookup - Find
Find the nth smallest number
Lookup - Find
Find the nth smallest number with 1 condition
Lookup - Find
Find the smallest number
Lookup - Find
Find the smallest number excluding zero
Lookup - Find
Find the smallest number with 1 condition
Lookup - Find
Find the smallest number with multiple conditions
Lookup - Find
Find the unique values in a range
Lookup - Find

Find a matching value

??

 A
1{=IF(OR(ValueToFind = CellRange),"Match","No Match")}
??

Find a matching value case sensitive

??

 A
1=IF(EXACT("better",HLOOKUP("better",Table_Range,1)) = TRUE, "found", "not found")
2=IF(EXACT("better",LOOKUP("better",Table_Range,1)) = TRUE, "found", "not found")
3=IF(EXACT("better",VLOOKUP("better",Table_Range,1)) = TRUE, "found", "not found")
4=IF(EXACT("better",INDEX(Table_Range,MATCH("better",Table_Range,0), 1) = TRUE, "found", "not found")
You can use the LOOKUP function to compare values to find an exact match, but the match is not case-sensitive.
To accomplish a case sensitive lookup you can combine the LOOKUP function with the IF and EXACT functions.
There are a number of ways to do this:
HLOOKUP and EXACT
LOOKUP and EXACT
VLOOKUP and EXACT
INDEX, MATCH and EXACT

Find a particular occurrence

??

 A
1copy from image
2=INDEX(B2:C14, SMALL( IF(B2:B14="match", ROW(B2:B14)-ROW(B2)+1, ROW(B14)+1),"row_item"),"col_index")
3=INDEX(B2:C14,SMALL( IF(B2:B14="Russell", ROW(B2:B14)-ROW(B2)+1,ROW(B14)+1),2),2) = 21
4=INDEX(B2:C14,SMALL( IF(B2:B14="Russell",ROW(B2:B14)-ROW(B2)+1, ROW(B14)+1),3),2) = 18
5=INDEX(B2:C14,SMALL( IF(B2:B14="Russell", ROW(B2:B14)-ROW(B2)+1, ROW(B14)+1), COUNTIF(B2:B14,"Russell")),2) = 18
**Particular Occurrence
The VLOOKUP and HLOOKUP functions can only be used to find the first occurrence of an element in a "table_array".
You can use an array formula to return a particular occurrence of a value when your table contains duplicate entries.
This is possible using a combination of the INDEX function and the SMALL function.
Lets imagine a simple table of data and lets suppose you want to find the Age of particular occurrence of "Russell" in this table.
The SMALL function can be used to obtain the particular occurrence you are looking for.
The ROW function can be used to obtain the row numbers for the items in the table.
The following formula will return a particular occurrence of a value in a column that contain duplicates.
"match" is the text string to match in the first (leftmost) column.
"row_item" is the occurrence number to return.
"col_index" is the column number to return in the table.
**2nd Occurrence
The following formula will return the "Age" of the second occurrence of Russell in the table.
ROW(B2:B14) returns the row numbers corresponding to all the items in the table: {2,3,4,5 ..}
ROW(B2) returns the row number of the first item in the table: {2}
Subtracting the two gives you an array starting from zero {0,1,2,3, ..} for as many rows there are in the table.
The +1 just re-indexes the array to start at 1 instead of zero: {1,2,3,4, ..}
ROW(B14) + 1 returns the row number that is directly below the table.
This is to ensure that when the IF function does not find a match the (row) number returned is larger than any row in the table.
In this case the number 15 is returned.
In this example we are looking for all the occurrences of "Russell" so the IF function will return the following array:
{15,15,3,15,15,15,7,15,15,15,11,15,15}
We are looking for the 2nd occurrence so the SMALL function returns the second smallest number which is 7.
This row number is then passed to the INDEX function to return the value in the Age column, column 2.
**3rd Occurrence
The following formula will return the "Age" of the third occurrence of Gary in the table.
**Last Occurrence
If you want to find the last occurrence of an item in a list then you can replace the "row_item" with the COUNTIF function.
This function returns the total number of values in a range that match a particular item.

Find the closest match

??

 A
1{=INDEX(CellRange,MATCH(SMALL(ABS(CellAddress - CellRange),1),ABS(CellAddress - CellRange),0))}
??

Find the last entry in a column

??

 A
1{=INDIRECT(ADDRESS(MAX((A1:A4000<>"")*ROW(A1:A4000)),COLUMN(A1:A4000),4))}
You can retrieve the last entry in a column - what about blank cells ??
This formula will not work if you try and use the whole column reference A:A for the range.
Also try and only reference the range that will potentially contain data, in this example we have used the range A1:A4000
Try and limit the last row to whatever is sensible but is as small as possible. This will prevent the formula taking an unnecessary amount of time to calculate.

Find the n smallest numbers

This shows you how to find the three smallest values in a range.

 A
1={SMALL(B2:D5, {1,2,3})}
This must be entered into 3 cells

Find the nth smallest number

??

 A
1=SMALL(A1:A10,2)
2=SMALL(A1:A10,4)
3{=SMALL( IF( ISNUMBER( B2:B10), IF (ROW (B2:B10) = MATCH(B2:B10, B2:B10, 0), B2:B10) ), 3)}
This shows you how to find the fourth smallest value
This shows you how to find the second smallest value
This shows you how to find the third smallest value, ignoring any duplicates

Find the smallest number

??

 A
1{=ADDRESS(MIN(IF(CellRange=MIN(CellRange),ROW(CellRange), "")), COLUMN(CellRange))}
??

Find the unique values in a range

This formula returns the number of unique items in a worksheet range "=SUM(1/COUNTIF(Rge,Rge))

 A
1SUM(COUNTIF(RgemRge)/IF(NOT(COUNTIF(rge,Rge)),1,COUNTIF(Rge,Rge))^2).
This will only work if the range does not contain blank cells
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited Top