Excel Formulas Part 2 and Lookups

Continuing from the last post, here are more formulas I'm using to handle data in Excel:

If I am doing substitution, first of all I would be changing the the date cell format to text:

Then comes the substitution, in this case I would like to change the date format to this: dd-mm-yyyy:

Note that only the front instance is being changed, that's because I specified the change to be made at only the first instance of "/", as displayed as 1 in the formula.

Now to test this again, I tweaked this formula to make the change to the 2nd instance only:

Now to do the full change, I did not specify an instance in the formula:

There are some more formulas that I would also be using, like MIN, MAX and TRIM, perhaps in the future I'd be able to demonstrate them. Now time to move on to the lookups.

I'll be using Xlookups if I wanted to search for a value from a specified column or columns that are side-by-side. In this case I would like to find out what is the Fuel Price from this date:

Xlookups is also having the advantage over Vlookups in terms of shifting column positions, in the case of other new columns being added into the table. Changing of column positions does not affect my Xlookups cell range.

As for Vlookups, this is a better lookup if I am searching through a wide range of columns to arrive at the result. Does not matter if the intended results are columns apart, Vlookup does the search through the specified table range:

Searching for the CPI on this particular date:

FALSE returns the exact value that we are searching for, in this case it's 217.6767.

And this below shows the columns and column range of the table that I was working on:

And again, pretty neat compilation of csv files from this contributor, will be using these csv files for future analysis as well.

Note: This 'Features data set' file was converted to .xlsx before handling my formulas.