Index Match Examples

GC

BPM Analytical Empowerment Pty Ltd INDEX MATCH Examples Go to Table of Contents

Primary Developer: Liam Bastick General Cover Notes: Some basic illustrations of how INDEX and MATCH may be used. To e-mail author: [email protected] www.bpmglobal.com Look for the yellow cells

C

Table of Contents INDEX MATCH Examples Go to Cover Sheet

é Section & Sheet Titles 1. Various INDEX MATCH Examples a. Balance Sheet - Looking For Misbalances b. Two Dimensional INDEX Example c. MATCH Example d. Case Sensitive MATCH Example e. Multiple Criteria INDEX MATCH Example f. Traffic Light Reporting: Grading Example

Total Pages:

Page 3 4 5 6 7 8 9

9

SC

Various INDEX MATCH Examples Section 1. INDEX MATCH Examples Go to Table of Contents

çè

Section Cover Notes: Answer to this month's query, and various illustrations of INDEX MATCH.

15673300.xls Examples_SC Printed: 17:31:50 on 03/19/2009

Page 3 of 10

FO

Balance Sheet - Looking For Misbalances INDEX MATCH Examples Go to Table of Contents

é

çè Month Ending Month

Jan-09 M1

Feb-09 M2

Mar-09 M3

Apr-09 M4

May-09 M5

Jun-09 M6

Jul-09 M7

Aug-09 M8

Sep-09 M9

Net Assets

100.0

110.0

120.0

130.0

137.8

150.0

160.0

170.0

179.9

Total Equity

100.0

110.0

120.0

130.0

140.0

150.0

160.0

170.0

180.0

-

-

-

-

1

-

-

-

1

Balance Sheet Summary

Balance Check:

Error Message:

Err:504

Error Reporting Options INDEX MATCH LOOKUP HLOOKUP

15673300.xls Balance_Sheet_Solution_FO Printed: 17:31:51 on 03/19/2009

Err:504 May-09 Err:502

Ä Err:504 Ä

Page 4 of 10

Oct-09 M10

Nov-09 M11

Dec-09 M12

190.0

200.0

210.0

190.0

200.1

210.0

-

1

-

15673300.xls Balance_Sheet_Solution_FO Printed: 17:31:51 on 03/19/2009

Page 5 of 10

BA

Two Dimensional INDEX Example INDEX MATCH Examples Go to Table of Contents

é

çè Two Dimensional INDEX Example

1 2 3 4 5 6 7 8 9 10 11

11 12 13 14 15 16 17 18 19 20 21

1 F 1 8 15 22 29 36 43 50 57 64 71

2 G 2 9 16 23 30 37 44 51 58 65 72

3 H 3 10 17 24 31 38 45 52 59 66 73

4 I 4 11 18 25 32 39 46 53 60 67 74

5 J 5 12 19 26 33 40 47 54 61 68 75

6 K 6 13 20 27 34 41 48 55 62 69 76

7 L 7 14 21 28 35 42 49 56 63 70 77

INDEX Assumptions Row Column

INDEX

4 5 26

15673300.xls Two_Dim_INDEX_Example_BA Printed: 17:31:51 on 03/19/2009

Page 6 of 10

BA

MATCH Example INDEX MATCH Examples Go to Table of Contents

é

çè MATCH Example Lookup Array E 12 13 14 15 16 17 18 19 20 21 22

1 2 3 4 5 6 7 8 9 10 11

F a b a c b d d e f a c

Lookup Value d

Match Type -

MATCH

6

15673300.xls MATCH_Example_BA Printed: 17:31:51 on 03/19/2009

Page 7 of 10

BA

Case Sensitive MATCH Example INDEX MATCH Examples Go to Table of Contents

é

çè Case Sensitive MATCH Example Data Word CIMA CIMa CImA CIma CiMA CiMa CimA Cima cIMA cIMa cImA cIma ciMA ciMa cimA cima

Value 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Lookup Value Cima

15673300.xls Case_Sens_MATCH_Example_BA Printed: 17:31:51 on 03/19/2009

Err:504

Formula is an array function, i.e. CTRL + SHIFT + ENTER in Windows, COMMAND + RETURN for Macintosh

Page 8 of 10

BA

Multiple Criteria INDEX MATCH Example INDEX MATCH Examples Go to Table of Contents

é

çè Multiple Criteria INDEX MATCH Example Data Month January January January January January January February February February February February February

Car Holden Holden Ford Ford Toyota Toyota Holden Holden Ford Ford Toyota Toyota

Colour Red Blue Red Blue Red Blue Red Blue Red Blue Red Blue

Code ABC001 ABC002 ABC003 ABC004 ABC005 ABC006 ABC007 ABC008 ABC009 ABC010 ABC011 ABC012

Lookup Information Month: January Car: Toyota Colour: Red

Code MULTIPLE CRITERIA:

15673300.xls Multiple_Criteria_Example_BA Printed: 17:31:51 on 03/19/2009

Err:504

Formula is an array function, i.e. CTRL + SHIFT + ENTER in Windows, COMMAND + RETURN for Macintosh

Page 9 of 10

BA

Traffic Light Reporting: Grading Example INDEX MATCH Examples Go to Table of Contents

é

ç Traffic Light Reporting: Grading Example Data Required Score 1 2

Grade Red Amber Green

Result and Reporting Actual Result GRADE

15673300.xls Grading_Example_BA Printed: 17:31:51 on 03/19/2009

Red

Uses match_type 1 implicitly

Page 10 of 10