The 2 Most Important

Excel Functions AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 12: INDEX/MATCH Formulas in Excel

With INDEX/MATCH you can automate your invoices, quotation workbooks, business models. You can also develop reports with data coming from many different sources. With these formulas you can virtually create relational databases in Excel and develop reports downstream. They are more powerful and versatile than LOOKUP formulas.

The quantities sold are in one data set, the name and the address of the client is in another set of data and then the product description is in a third set of data...no problem, a single INDEX/MATCH formula copied all the way down your column solves this problem. You now have a data set including sales, products and clients.

Stop entering data manually in your workbooks and reports use the easy to master INDEX/MATCH formula.

The INDEX/MATCH formula is a formula using 2 functions INDEX and MATCH.

With the example below, the formula =INDEX(A2:E5,1,2) would return " Tiger Auto" because the formula translated in plain English says, what is the value found in the rage " A2:E5" in the first row and the second column.

The formula =MATCH(B11,A2:A5,0) in cell B12 would return 2 because the value in B11 (86598) is on the second row of the range A2:A5. The zero at the end of the formula tells Excel that you want an exact match. If you were looking for a number, a 1 instead of a zero would tell Excel to use the next higher value as a -1 would mean use the next lower value.

With MATCH  you don't have to sort the range or use FALSE or TRUE.

Now let us replace the row number in the INDEX formula by the MATCH formula:
=INDEX(A2:E5,MATCH(B11,A2:A5,0),2)
and whenever you change the value in cell B11, a new value appears in cell B12.

Here are some formulas combining INDEX and MATCH functions.

Tired of typing names, addresses, cities and states on invoices?  

Each time you enter a Client #, the name, address, city and State are automatically modified. Imagine the time you save and the number of errors that you avoid when you complete your invoices. You can do the same with your products entering the name and extracting the unit price, the product number from a table.

You can also see how it is used to develop an invoice where the address of the client and the decription of the products are automated.