Excel Index and Search Tool (Click on the buttons below)

Excel Lesson 18 of 30: INDEX/MATCH Formulas

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.

 MORE on INDEX/MATCH and Drop-Down Lists If like me you like to assemble all your list on a single sheet and you want to be able to refer to them in drop-downs on other sheets there is a way. Discover it in a practicle application: an invoice. See how it is done for a client's list and parts details. Open "excel-invoice.xls"

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.

 A B D D E 1 Client # Name Address City State & ZIP 2 36596 Tiger Auto 33 Woods Miami FL 10230 3 86598 Phil Lumber 555 Makes Boston MA 34567 4 58971 David Eng. 1200 Duvall Charleston SC 10004 5 87456 Stewart Inc. 5673 Payne San Francisco CA 27002

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

 A B 11 Client #: 86598 12 Name: =INDEX(A2:E5,MATCH(B11,A2:A5,0),2) 13 Address: =INDEX(A2:E5,MATCH(B11,A2:A5,0),3) 14 City: =INDEX(A2:E5,MATCH(B11,A2:A5,0),4) 16 State & ZIP =INDEX(A2:E5,MATCH(B11,A2:A5,0),5)

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.

Even better, if you also have a database for your products you can enter the product number, and the description and unit price appear automatically avoiding even costlier mistakes.

 More on INDEX/MATCH The powerful INDEX/MATCH formula explained step by step with examples. You will never use VLOOKUP anymore. Also, INDEX/MATCH with multiple criteria. Don't just look up in one column look up in 2, 3 or more.
 Excel Index and Search Tool (Click on the buttons below)