How to using Index Match Formula

Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations).  INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modelling to the next level.

INDEX returns the value of a cell in a table based on the column and row number.

MATCH returns the position of a cell in a row or column.

Here is an example of the INDEX and MATCH formulas combined together.  In this example, we look up and return a person’s height based on their name.  Since name and height are both variables in the formula, we can change both of them!

A               B              C            D             E

                                 1            2             3

                 1           Name     Height     Weight

                 2           Rohit        6.2           185

                 3           Kohli        5.9           170

                 4           Kumble    5.8           175

                 5           Anil          5.5           145

                 6           Sami        6.1           210

                 7           Hardik      6.0           180

Kumble    =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

Height      Answer is 5.8 

Advance Excel : Advance Filter

•           This example teaches you how to apply an advanced
  Click on this link for view example
  
  
  filter in Excel to only display records that meet complex criteria.

•           When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set.

•           And Criteria

•           To display the sales in the USA and in Qtr 4, execute the following steps.

•             1. Enter the criteria shown below on the worksheet.

•             2. Click any single cell inside the data set.

•             3. On the Data tab, in the Sort & Filter group, click Advanced.

•             4. Click in the Criteria range box and select the range A1:D2 (blue).

•             5. Click OK.

•           Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates).

•           Result.

•           No rocket science so far. We can achieve the same result with the normal filter. We need the Advanced Filter for Or criteria.