Number Format (Simple)

I am sure that you have come across many times to one of the most common issues in Excel, numbers which are formatted as TEXT, therefore when you are trying to use them as a reference it won't work, so Microsoft created a simple solution using the formula VALUE.

This is a very simple and straightforward way to convert the TEXT to a Number, however and as you know Microsoft also offer the solution using the drop down when you "Hoover" on the small triangle located on the top right hand side of the cell:

But then again you need to select the full range and depending of the number of items selected it may take a longer time to convert, so and with the "VALUE" formula you can speed up the process.

To use the "VALUE" formula you just need to follow some simple steps:

1. Let's imagine that the range of numbers formatted as Text are in column C row 6.
2. Go to an empty column or Insert column next to the column where the range is.
3. Now in the column and row 6, enter =VALUE(C6)
4. Copy the formula to cover the required range.
5. Simple, your new range can now be used as a reference for any formula that you require.

 

Use INDEX and MATCH functions for table lookup

The table lists some products and from where they are originally from, and the quantity that my establishment received.

How can we look up the location from where the Product originated from? 

Normally we just create the VLOOKUP function as

“=VLOOKUP("Oranges",$A$2:$C$9,2,0)”

The result would be “Portugal” as we ask to look up “Oranges” within the table and to give the result in column 2.

Nothing wrong with this, BUT, what about if you want to know the reverse, like, from which Location is the product “Oranges”, well here is a bit more tricky as the function VLOOKUP do not look to the left, so using the INDEX AND MATCH function is better and more powerful.

So how will we do it?

For this example we use the syntax:

The MATCH function, syntax:  MATCH(lookup_value;array;match_type)

Lookup_value: is the value you want to match in lookup_array. It can be a value like =MATCH(10,A1:A15,0)or a cell reference like =MATCH(B2,A1:A5,0).
Lookup_array: is a contiguous range of cells containing possible lookup values. Lookup_array must be an array like this: =MATCH(“b”,{“a”,”b”,”c”},0) or an array reference like MATCH(7,A1:A15,0).
Match_type: is the number -1, 0, or 1 as seen here: =MATCH(B2,A1:A5,0). Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

=INDEX($A$2:$A$9,MATCH("Italy",$B$2:$B$9,0)) = “Tomatoes”, meaning:

• *        INDEX will the range of the result column in the example I want to get the “Product”
• *        MATCH is what you want to look up, in the example is the “Location”, you also need to define the range of the MATCH, the “0” represents EXACT MATCH, so it gives you exactly what you want.

There are loads more of INDEX and MATCH possibilities, I will come back to this subject in the near future.