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. […]
Welcome! I am 