from Pierre Leclerc at http://www.excel-vba.com





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.

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.

EXAMPLE

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.

EXAMPLE

Here is an invoice. You select the name of a client in a drop-down list and his address appears automatically. You select a product number in a drop-down list and its description, unit and unit price appear automatically. No more typing and no more typos.