Advanced LOOKUP in Excel

INDEX and MATCH are two very powerful functions for performing advanced lookups in Excel. Using VLOOKUP, HLOOKUP or LOOKUP, you are restricted to performing lookups on either a column range or row range and NOT BOTH in the same formula. This can be quite restrictive especially when the value to be looked up is dependant on using both column range and row range simultaneously.

INDEX and MATCH can perform lookups where the other lookup functions either cannot work or can work by creating a very complex formula. For example if your data layout is in the form of a matrix (x cols by y rows) and you need to extract certain selected values based on their col position and row position, then the above two functions will perform this process in one straight forward formula.

Data imported from SAP converts dates to text

It is a common problem to find that when you import data from other data sources into Excel, the dates are converted to text. Most times this is data being retrieved for the monthly reporting cycle and there are very strict deadlines to produce the management reports. Most Business Analysts spend their nights in the office to address such mundane problems.

Date problems are best addressed using a combination of Excel Date and Text functions. Sometimes you may need to incorporate a Excel Boolean functions. An example is =IF(ISERROR(DATE(YEAR(D2),MONTH(D2),DAY(D2))),DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)),DATE(YEAR(D2),MONTH(D2),DAY(D2)))