Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both column and row part of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function.   The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 or

INDIRECT function

The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, reference text is interpreted as an A1 style reference i.e. the default style of referencing cells in Excel. If FALSE, reference text is interpreted as an

How to MATCH numbers formatted as text in Excel

You will sometimes encounter errors while trying to MATCH or LOOKUP data in Excel if your numbers are formatted as numbers in one of your tables, and as text in another table. While special formats are available in Excel, they are relatively rarely used and are limited. Some “numbers” such as identification numbers are more often stored as text. This is done in order to add leading zeros, hyphens and other characters to that numbers. However, if we try to

Top 10 lists in Excel

When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values. Consider the following example: Here, we have a table with the invoices from the 29th of January. There are 904 invoices for that day. Values in the invoice column are unique as those are invoice numbers. Values in the user column are not unique, as some users have multiple invoices on that day, and values in the team column are not unique

Combining SUMIFS with INDEX MATCH

You will sometimes want to retrieve data from a table with values both in rows and columns and based on multiple (three or more criteria). There is, as usual, more than one way to do this. However, most of those ways require you to use something called array formulas – in essence, you will have to press Ctrl Shift Enter combination on your keyboard every time you want to calculate that formula. Combining SUMIFS with INDEX MATCH enables us to

Lookup with unique identifiers

INDEX function The INDEX function returns the value from a range of cells based on row and column specified in the function arguments. We are simplifying here, the INDEX function can actually return values from one or more arrays – i.e. broader term for lists of data which includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel.   Syntax of the INDEX function is as follows: