Excel functions and formulas         

 

 

Excel tips on functions and formulas

Excel Functions and Formulas contents

Excel functions worksheets

Excel and VBA Expert

 

 

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.

 

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. 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.  See all these with step by step detailed instructions in the small tutorial on Invoice.


Excel Functions and Formulas Sitemap

 

 

 

Excel tips on functions and formulas

Excel Functions and Formulas contents

Excel functions worksheets

Excel and VBA Expert

 

SUMPRODUCT    INDEX/MATCH    SUBTOTAL    ISERROR    HLOOKUP    LOOKUP    VLOOKUP
AVERAGEIF    AVERAGEIFS   COUNTIF    COUNTIFS    SUMIF    SUMIFS    IFERROR