'LOOKUP function' category
Subcategories
Match a range value containing both text and numerical characters
This article demonstrates how to match a value containing both text and digits to ranges. The search value is specified […]
This article demonstrates how to match a value containing both text and digits to ranges. The search value is specified […]
AVERAGE ignore blanks
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
SUMPRODUCT and nested IF functions
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
Sum by group
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. =LOOKUP(2,1/(COUNTIF($B$12:B12,$B$3:$B$10)=0),$B$3:$B$10) Copy cell B13 […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. =LOOKUP(2,1/(COUNTIF($B$12:B12,$B$3:$B$10)=0),$B$3:$B$10) Copy cell B13 […]
Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Extract duplicate values with exceptions
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
Find last value in a column
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
Lookup and match last value – reverse lookup
This article shows a formula that performs a reverse lookup and returns the corresponding value based on the last matching […]
This article shows a formula that performs a reverse lookup and returns the corresponding value based on the last matching […]
Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Finds a value in a sorted cell range and returns a value on the same row.
Find last matching value in an unsorted list
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Find date range based on a date
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
How to use VLOOKUP/XLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
VLOOKUP – Return multiple unique distinct values
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
Remove duplicates based on date
Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]
Question: Column B has dates Column C as data B5 : 1/1/2010 : 5000 B6 : 2/1/2010 : 4000 B7 […]
Extract unique distinct year and months from dates
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
Extract unique distinct values if the value contains the given string
This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]
This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Filter unique distinct values where adjacent cells contain search string
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Filter unique values and sort based on adjacent date
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
Extract a list of duplicates from two columns combined
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Create a list of duplicates where adjacent cell value meets a condition
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Create a unique distinct list based on criteria
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
Extract unique values from two columns
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list and ignore blanks
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values