## Archive for MATCH function

DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

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 […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]

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 […]

The array formula in cell D12 matches two values in two columns each and returns a value on the same […]

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 […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

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 […]

Identify the position of a value in an array.

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 […]

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 […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]