INDEX / MATCH Question

[Dthmtlgod]

I need to return a value from another worksheet based on three criteria.
Can this be done using Index/Match?

Something like this. I don't want to use a Pivot Table

=INDEX(SHEET1!A22000,(MATCH(B2,SHEET1!C2:C2000,0),MATCH(A4,SHEET1!(B2:B200
0,0),MATCH(D3,SHEET!D22000),0)),1)

Thanks in advance

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(SHEET1!A2:A2000,MATCH(1,(SHEET1!C2:C2000=B2)*(SHEET1!B2:B200=A4)
*(SHEET!D22000=D3),0))

 

[Domenic]

Hi,

try,

=INDEX(Sheet1!A22000,MATCH(1,(Sheet1!C2:C2000=B2)*(Sheet1!B2:B2000=A4)*
(Sheet1!D22000=D3),0))

to be entered using CTRL+SHIFT+ENTER

Hope this helps!

 

[Dthmtlgod]

Thank you both.

Hi,

try,

=INDEX(Sheet1!A22000,MATCH(1,(Sheet1!C2:C2000=B2)*(Sheet1!B2:B2000=A4)*
(Sheet1!D22000=D3),0))

to be entered using CTRL+SHIFT+ENTER

Hope this helps!

 

[Dthmtlgod]

{=INDEX(Feeder!$A$2:$D$2000,MATCH(1,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C
$2000=A4)*(Feeder!$B$2:$B$2000=C2),0))}

I modified the code a little bit, I am receiving the #NA error msg.

 

[Frank Kabel]

Hi
try
{=INDEX(Feeder!$A$2:$A$2000,MATCH(1,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C
$2:$C
$2000=A4)*(Feeder!$B$2:$B$2000=C2),0))}

 

[Dthmtlgod]

I changed the match(1, to match(0, I needed to grab the data in the first
column.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C$
2000=A5)*(Feeder!$B$2:$B$2000=C2),0))
^^
Changed from 1
to 0

It works somewhat, but now it returns the same value in cell Feeder!A2
regardless of what I am search for.

Thanks,
Doug

 

[Frank Kabel]

Hi
this won't work. If you still receive an #NA error it just means that
Excel does not find an exact match. Sou plae '1' at the beginning of
your MATCH function and check if there's really a record which mets all
3 criteria (check formats for example, etc.)

 

[Domenic]

Doug,

It looks like you haven't changed the first part of the formula, as
Frank pointed out.

Try Frank's formula again,

{=INDEX(Feeder!$A$2:$A$2000,MATCH(1,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C
$2:$C$2000=A4)*(Feeder!$B$2:$B$2000=C2),0))}

 

[Aladin Akyurek]

Also...

In E2 enter & copy down

=B2&CHAR(127)&C2&CHAR(127)&D2

With lookup values, C2, A5, and B1, you can have ordinary...

=INDEX($A$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))

which would work lots faster.

I changed the match(1, to match(0, I needed to grab the data in the first
column.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C$
2000=A5)*(Feeder!$B$2:$B$2000=C2),0))
^^
Changed from 1
to 0

It works somewhat, but now it returns the same value in cell Feeder!A2
regardless of what I am search for.

Thanks,
Doug

[...]

 

[Dthmtlgod]

Thank you

Also...

In E2 enter & copy down

=B2&CHAR(127)&C2&CHAR(127)&D2

With lookup values, C2, A5, and B1, you can have ordinary...

=INDEX($A$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))

which would work lots faster.

I changed the match(1, to match(0, I needed to grab the data in the first
column.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C$

2000=A5)*(Feeder!$B$2:$B$2000=C2),0))
^^
Changed from 1
to 0

It works somewhat, but now it returns the same value in cell Feeder!A2
regardless of what I am search for.

Thanks,
Doug

[...]

 

[Dthmtlgod]

I have it working. One last quick question.
For the ones it doesn't find a match. How can I populate it with a zero
rather than #N/A.

Thanks,
Doug

Also...

In E2 enter & copy down

=B2&CHAR(127)&C2&CHAR(127)&D2

With lookup values, C2, A5, and B1, you can have ordinary...

=INDEX($A$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))

which would work lots faster.

I changed the match(1, to match(0, I needed to grab the data in the first
column.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C$

2000=A5)*(Feeder!$B$2:$B$2000=C2),0))
^^
Changed from 1
to 0

It works somewhat, but now it returns the same value in cell Feeder!A2
regardless of what I am search for.

Thanks,
Doug

[...]

 

[Frank Kabel]

Hi
try
=IF(ISNA(MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0)),0,INDEX($A
$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))
)


--
Regards
Frank Kabel
Frankfurt, Germany

I have it working. One last quick question.
For the ones it doesn't find a match. How can I populate it with a
zero rather than #N/A.

Thanks,
Doug

Also...

In E2 enter & copy down

=B2&CHAR(127)&C2&CHAR(127)&D2

With lookup values, C2, A5, and B1, you can have ordinary...

=INDEX($A$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))

which would work lots faster.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$
2:$C$

2000=A5)*(Feeder!$B$2:$B$2000=C2),0))

^^
Changed from 1
to 0

It works somewhat, but now it returns the same value in cell
Feeder!A2 regardless of what I am search for.

Thanks,
Doug

[...]

 

[Aladin Akyurek]

Lets keep performance as fast as possible by using a 2-cell approach...

F2:

=MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0)

E2:

=IF(ISNUMBER(F2),INDEX($A$2:$A$2000,F2),0)

E2 is the result cell.

I have it working. One last quick question.
For the ones it doesn't find a match. How can I populate it with a zero
rather than #N/A.

Thanks,
Doug

Also...

In E2 enter & copy down

=B2&CHAR(127)&C2&CHAR(127)&D2

With lookup values, C2, A5, and B1, you can have ordinary...

=INDEX($A$2:$A$2000,MATCH(C2&CHAR(127)&A5&CHAR(127)&B1,$E$2:$E$2000,0))

which would work lots faster.

=INDEX(Feeder!$A$2:$D$2000,MATCH(0,(Feeder!$D$2:$D$2000=B1)*(Feeder!$C$2:$C$

from
1

to 0

It works somewhat, but now it returns the same value in cell Feeder!A2
regardless of what I am search for.

Thanks,
Doug

[...]