Using the array formulas to pull specific text from data table

KYLE_

New Member
Joined
Apr 30, 2019
Messages
2
Hello All,

I have a set of dynamic data that is somewhat inconsistent. My objective is to pull text from the set of data based on a specific unchanging criteria. However, at times -as I've already indicated because of the dynamic data- not all of the criteria is able to be fulfilled and it ends up displaying an error. I will try to display a small sample size that best represents the data, along with my array formula that I am trying to use to pull the data. if someone could suggest a solution that would be excellent.

=INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="N10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)&"_"&INDEX('EXPORTED FAR'!A:N,MATCH(1,('EXPORTED FAR'!A:A=$B32)*('EXPORTED FAR'!B:B="D10")*('EXPORTED FAR'!D:D=$B$2)*('EXPORTED FAR'!E:E=$B$3),0),14)

THE CRITERIA: THE DATE, 4 DIGIT NUMBER, 3 DIGIT CODE, 3 DIGIT CODE

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EXTRA CRITERIA[/TD]
[TD]3416[/TD]
[TD]S42[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]COMMENTS (RESULTS FROM FORMULA with data below)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/26/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/27/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 396"]
<tbody>[TR]
[TD="class: xl65, width: 396"]w/s , mck , g/s _c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]4/28/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]#value[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 626"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]3 DIGIT CODE [/TD]
[TD] 4 DIGIT CODE[/TD]
[TD] 3 DIGIT CODE[/TD]
[TD]COMMENTS[/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/26/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]c/s mk to rm inst g/s prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
[TR]
[TD="align: right"]4/27/2019 [/TD]
[TD] N10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416[/TD]
[TD] S42[/TD]
[TD]w/s , mck , g/s [/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2019[/TD]
[TD] D10[/TD]
[TD="align: right"]3416 [/TD]
[TD]S42[/TD]
[TD]c/s comp g/s mk pad prep d/b 8' advancement[/TD]
[/TR]
</tbody>[/TABLE]

sorry for the format^

You will notice in the last two rows the two entries for the date 4/28/2019 both have D10 as the second 3 digit code, this is not an error. However, this is what causes the error in my array formula. The array formula is looking for an entry with D10 and an entry with N10--> for clarity this code refers to a dayshift and a nightshift. Sometimes in some working areas the only work a dayshift/nightshift whatever the case may be. So I'm not always able to fulfill the criteria and then its puts out an error. Or sometimes like also displayed with the data table above i have two comments for the same criteria and i would like both comments displayed. If more clarity is required please let me know.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

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