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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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