IFERROR(Index(match array

[Kmitchell]

The below formula only works with an array. However there are some #N/As. And when I try to add IFERROR using the second formula all the found matches turn to blanks. Any ideas what I could be doing wrong?

=INDEX(DETAIL!$F$2:$F$7474,MATCH(SUMMARY!$A3&E$2,DETAIL!$A$2:$A$7474&DETAIL!$D$2:$D$7474,0))


IFERROR(INDEX(DETAIL!$F$2:$F$7474,MATCH(SUMMARY!$A3&D$2,DETAIL!$A$2:$A$7474&DETAIL!$D$2:$D$7474,0)),"")

 

[Marcelo Branco]

The below formula only works with an array. However there are some #N/As. And when I try to add IFERROR using the second formula all the found matches turn to blanks. Any ideas what I could be doing wrong?

=INDEX(DETAIL!$F$2:$F$7474,MATCH(SUMMARY!$A3&E$2,DETAIL!$A$2:$A$7474&DETAIL!$D$2:$D$7474,0))


IFERROR(INDEX(DETAIL!$F$2:$F$7474,MATCH(SUMMARY!$A3&D$2,DETAIL!$A$2:$A$7474&DETAIL!$D$2:$D$7474,0)),"")

Is this a typo (in red)?

A small data sample along with expected result would be helpful.

M.

 

[Kmitchell]

Hi - not this is not an error, there are multiple matches. I may have copied that formula from a different column. I am unsure on how to add a data sample? Please advise or redirect on proper steps to add in the future.

 

[Marcelo Branco]

To add a data sample - see section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

The easiest way: select a relevant part of your worksheet (5 to 10 rows), copy (Ctrl+C) and paste (Ctrl+V) in the forum reply page

M.

 

[Kmitchell]

Ah thanks for sending that link - unfortunately its blocked

This a sample of the "detail" tab.
provider prov_full_name NP_ID sequence specialty specialty_name
123456 Smith, John 1218854000 1 RADI Diagnostic Radiology
125746 Sam, Smith 1140165000 2 BEHA Behavioral Health

The Summary tab columns look like this:
A B C D E F G
provider Prov_Full_name NP_ID 1 2 3 4

 

[Marcelo Branco]

Not sure i understand your layout.

See if this does what you need

Sheet DETAIL

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Provider​

[/TD]
[TD]

prov_full_name​

[/TD]
[TD]

NP_ID​

[/TD]
[TD]

sequence​

[/TD]
[TD]

specialty​

[/TD]
[TD]

specialty_name​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

123456​

[/TD]
[TD]

Smith, John​

[/TD]
[TD]

1218854000​

[/TD]
[TD]

1​

[/TD]
[TD]

RADI​

[/TD]
[TD]

Diagnostic Radiology​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

123456​

[/TD]
[TD]

Smith, John​

[/TD]
[TD]

1218854000​

[/TD]
[TD]

2​

[/TD]
[TD]

BEHA​

[/TD]
[TD]

Behavioral Health​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

125746​

[/TD]
[TD]

Sam, Smith​

[/TD]
[TD]

1140165000​

[/TD]
[TD]

2​

[/TD]
[TD]

BEHA​

[/TD]
[TD]

Behavioral Health​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet SUMMARY

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Provider​

[/TD]
[TD]

Prov_Full_name​

[/TD]
[TD]

NP_ID​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

123456​

[/TD]
[TD]

Smith, John​

[/TD]
[TD]

1218854000​

[/TD]
[TD]

Diagnostic Radiology​

[/TD]
[TD]

Behavioral Health​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

125746​

[/TD]
[TD]

Sam, Smith​

[/TD]
[TD]

1140165000​

[/TD]
[TD][/TD]
[TD]

Behavioral Health​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D3 copied across and down
=IFERROR(INDEX(DETAIL!$F$2:$F$7474,MATCH(1,IF(DETAIL!$A$2:$A$7474=$A3,IF(DETAIL!$D$2:$D$7474=D$2,1)),0)),"")
confirmed with Ctrl+Shift+Enter

Hope this helps

M.

 

[Kmitchell]

Nailed it! Thank you so much! The Match(1, what does that mean? I am not sure what I did wrong/