We have already seen a table lookup example using the OFFSET function.
Here, we perform a similar operation using INDEX and MATCH
The example below show how well INDEX and MATCH can work together.
MATCH will return the position of a particular value in an array, while INDEX returns the value of a particular position within an array. We see multiple examples of these functions in operation below.
PriceMatrix is C4:E6 Material is C3:E3 Item is B4:B6 Using these names in the formulas below makes for easier readability nad maintenance. For this reason, it is always good practice to take advantage of the ability to name arrays that will be used within formulas.
At first glance, the TEXT function may not sound very useful. Here are a few simple applications.
In the top example, without the TEXT function, the total is not formatted correctlt (as a currency). However, the TEXT function allows us to apply the correct format to the number component of the string.
In the second example, we see how the TEXT function allows us to reformat existing data – such as dates.