Index Match with 3 Criteria

lai_arceo · Sep 30, 2024

I want to generate data on my table (Rpt) based on 3 criteria from another sheet (DB). I got the first column working fine with an index match function but when I copy the formula in the next cell, it generates an #N/A error. Can someone help me with this please? Thank you in advance!

Sample sheet.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

1

35

36

37

38

39

2

Grp

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

3

1-1

0

2

0

2

0

2

0

2

0

2

0

4

1-2

0

4

0

7

0

4

0

1

0

7

0

8

0

8

0

8

0

5

1-3

0

3

0

3

0

2

0

1

0

2

0

2

0

6

1-4

0

2

0

1

0

2

0

1

0

1

0

1

0

1

0

7

1-5

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

1

0

8

1-6

0

2

0

2

0

2

0

2

0

2

0

9

1-7

0

1

0

1

0

1

0

1

0

1

0

1

0

10

1-8

0

DB

Sample sheet.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

WEEK

35

2

Grp

A

B

C

D

E

F

G

H

I

J

K

L

M

N

3

1-1

0

#N/A

Rpt

Cell Formulas
Range	Formula
B3:C3	B3	=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!A3:A96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))

etaf · Sep 30, 2024

so when you copy

=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!A3:A96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))

to the next column , it will become
DB!B3:B96,0 in the MATCH part

=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!B3:B96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))

Every thing else is FIXED

is that now the new column of data you now want to match
instead of
MATCH($A3,DB!A3:A96,0)
you are using
MATCH($A3,DB!B3:B96,0)

lai_arceo · Sep 30, 2024

etaf said:
so when you copy

=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!A3:A96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))

to the next column , it will become
DB!B3:B96,0 in the MATCH part

=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!B3:B96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))

Every thing else is FIXED

is that now the new column of data you now want to match
instead of
MATCH($A3,DB!A3:A96,0)
you are using
MATCH($A3,DB!B3:B96,0)

I have corrected my formula as follows:

Sample sheet.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

WEEK

35

2

Grp

A

B

C

D

E

F

G

H

I

J

K

L

M

N

3

1-1

0

#REF!

4

1-2

5

1-3

6

1-4

7

1-5

8

1-6

9

1-7

10

1-8

Rpt

Cell Formulas
Range	Formula
B3	B3	=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!$A$3:$A$96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(B$2,DB!$B$2:$BX$2,0))
C3	C3	=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!A3:A96,0),MATCH($I$1,DB!$B$1:$BX$1,0),MATCH(C$2,DB!$B$2:$BX$2,0))

I do not know why when I copy that to the next cell, it generates a #REF error.

etaf · Sep 30, 2024

try
=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!$A$3:$A$96,0),MATCH($I$1&B$2,DB!$B$1:$BX$1&DB!$B$2:$BX$2,0))

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

WEEK

36

2

Grp

A

B

C

D

E

F

G

H

I

J

K

L

M

N

3

1-1

Sheet2

Cell Formulas
Range	Formula
B3:H3	B3	=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!$A$3:$A$96,0),MATCH($I$1&B$2,DB!$B$1:$BX$1&DB!$B$2:$BX$2,0))

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

1

35

36

2

Grp

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

*'

3

1-1

12

14

100

25

30

0

2

0

A36

B36

C36

0

2

0

4

1-Feb

0

4

0

7

0

4

0

1

0

7

0

5

1-Mar

0

3

0

3

0

6

1-Apr

0

2

0

1

0

2

0

1

0

7

1-May

0

1

0

1

0

1

0

1

0

8

1-Jun

0

2

0

2

0

9

1-Jul

0

1

0

1

0

1

0

10

1-Aug

0

DB

will only be on the dropbox share for a few days

Dropbox

www.dropbox.com

lai_arceo · Sep 30, 2024

etaf said:
try
=INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!$A$3:$A$96,0),MATCH($I$1&B$2,DB!$B$1:$BX$1&DB!$B$2:$BX$2,0))

Book1
A B C D E F G H I J K L M N O P
1 WEEK 36
2 Grp A B C D E F G H I J K L M N
3 1-1 A36 B36 C36 0 0 0 2
Sheet2
Cell Formulas
Range Formula
B3:H3 B3 =INDEX(DB!$B$3:$BX$96,MATCH($A3,DB!$A$3:$A$96,0),MATCH($I$1&B$2,DB!$B$1:$BX$1&DB!$B$2:$BX$2,0))

Book1
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE
1 35 35 35 35 35 35 35 35 35 35 35 35 35 35 35 36 36 36 36 36 36 36 36 36 36 36 36 36 36 36
2 Grp A B C D E F G H I J K L M N *' A B C D E F G H I J K L M N *'
3 1-1 12 14 100 25 30 0 2 0 0 0 0 0 0 0 0 A36 B36 C36 0 0 0 2 0 0 0 0 0 0 0 0
4 1-Feb 0 4 0 0 0 0 7 0 0 0 0 0 0 0 0 0 4 0 1 0 0 7 0 0 0 0 0 0 0 0
5 1-Mar 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 0 0 0 0
6 1-Apr 0 2 0 0 0 0 1 0 0 0 0 0 0 0 0 0 2 0 0 0 0 1 0 0 0 0 0 0 0 0
7 1-May 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0
8 1-Jun 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0
9 1-Jul 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0
10 1-Aug 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
DB

will only be on the dropbox share for a few days

Dropbox

www.dropbox.com

You are amazing! This works! Thank you very much!

etaf · Sep 30, 2024

you are welcome

Index Match with 3 Criteria

lai_arceo

New Member

Excel Facts

etaf

Well-known Member

lai_arceo

New Member

etaf

Well-known Member

Dropbox

lai_arceo

New Member

Dropbox

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Index Match with 3 Criteria

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock