Archive for INDEX function
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Match a range value containing both text and numerical characters
Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array […]
Rearrange cells in a cell range to vertically distributed values
The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
How to extract numbers from a cell value
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
Identify all characters in a cell value
Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]
The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list […]
How to remove numbers from a cell value
The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]
INDEX and MATCH – multiple criteria and multiple results
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
INDEX MATCH with multiple criteria
The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Merge tables based on a condition
I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]
Gets a value in a specific cell range based on a row and column number.
Calculate average of last 10 data with possible blank cells
Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]
Remove duplicate text strings based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Dynamic team generator in excel
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]
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 […]
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Extract largest values from two columns
Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
Create a unique distinct list and sort based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
How to automatically fill all blanks with missing data or formula
Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]
