IFERROR(Index(match array

Kmitchell

Active Member
Joined
Feb 27, 2007
Messages
363
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Nailed it! Thank you so much! The Match(1, what does that mean? I am not sure what I did wrong/
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top