## INDEX function

Excel » Functions » Lookup and reference » INDEX function »

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

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

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 array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

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

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Gets a value in a specific cell range based on a row and column number.

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

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

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