MATCH and INDEX help

[Nanaia]

Can anyone assist with unpuzzling what I did wrong with this formula? I am still learning how to create MATCH and INDEX formulas. Because of the vast amount of information in the file I would prefer not sharing the cleaned file because of how long it would take to scrub it clean. If I must, I must, however it wouldn't be until much later in the day.

Backstory: Table2 (an Excel formatted table) is a job tracker spreadsheet that uses thousands of rows. The column headings that are relevant are "Complete" which has either "Y" or "N". "ENGST" which has either "YES" or is blank. "Sent to Engineer" which either has a date formatted as mm/dd/yy or is blank. "Line #" which has job numbers in it. Line # can have the same job number in it multiple times.

On a separate tab named "OUTSTANDING" is a list beginning in column H2 that is generated with what I call a SPILL or FILTER formula that pulls a list of numbers fed from Table2 that meets certain criteria and can change throughout the day. In column J of the OUTSTANDING tab I am trying to create a formula that looks at the number in H2 and searches Table2 for the same number and "Complete" must be "Y", "ENGST" must be "YES", "Sent to Engineer" must have a date in it. If all of these criteria are met, the formula should display whatever date is in the "Sent to Engineer" column. If the criteria is not met the formula should display "NOT SENT".
Here is the formula currently in J2 which only returns a blank cell when it should return the associated date from the "Sent to Engineer" column.

=IF(AND(INDEX(Table2[Complete],MATCH(OUTSTANDING!H2,Table2[Line '#],0))="Y",INDEX(Table2[ENGST],MATCH(OUTSTANDING!H2,Table2[Line '#],0))="YES",NOT(ISBLANK(INDEX(Table2[Sent to Engineer],MATCH(OUTSTANDING!H2,Table2[Line '#],0))))),INDEX(Table2[Sent to Engineer],MATCH(OUTSTANDING!H2,Table2[Line '#],0)),"NOT SENT")

 

[etaf]

how about a filter formula
this brings back 3 columns
=FILTER(J2:L11,(J2:J11="Y")*(K2:K11="YES")*(L2:L11<>""),"No Match")
in A2

BUT you only what
1 column the date

as you can see in A5
=FILTER(L2:L11,(J2:J11="Y")*(K2:K11="YES")*(L2:L11<>""),"No Match")

i have simplified considerably

i'm assuming you would get multiple dates if conditions are met

=FILTER(L2:L11,(J2:J11="Y")*(K2:K11="YES")*(L2:L11<>""),"No Match")

i just used column references - so not tables

Book17
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										complete	ENGST	sent to eng date
2	y	Yes	1/1/24							y	Yes	1/1/24
3										n		1/2/24
4										y		1/3/24
5	1/1/24									n	Yes	1/4/24
6										y	Yes
7										n
8										y
9										n	Yes
10										y	Yes
11										n
Sheet1

Cell Formulas
Range	Formula
A2:C2	A2	=FILTER(J2:L11,(J2:J11="Y")*(K2:K11="YES")*(L2:L11<>""),"No Match")
A5	A5	=FILTER(L2:L11,(J2:J11="Y")*(K2:K11="YES")*(L2:L11<>""),"No Match")
Dynamic array formulas.

IF NOT
why not give a much simpler example as i have done with expected results

 

[Nanaia]

This is the section of the OUTSTANDING tab. I guess you could call it a dashboard. Column H changes frequently. In column J I want to be able to see whether or not the number listed in column H was sent out at a glance.

 

[etaf]

ia H column a Unique entry for an employee - like an ID tag ?????

so would that appear in MY MOCK UP , in column H only once ???
if so , maybe a bit messy , and INDEX maybe a better way - BUT again will only pull back the first instance

=FILTER($L$2:$L$11,($J$2:$J$11="Y")*($K$2:$K$11="YES")*($L$2:$L$11<>"")*($H$2:$H$11=A2),"No Match")
if more then once maybe a splii error

if more then once - MAYBE a TEXTJOIN to bring back all the dates - SEE BELOW

Book19
	A	B	C	D	E	F	G	H	I	J	K	L
1	OUT FOR ENGST		TO ENG?					OUT FOR ENGST		complete	ENGST	sent to eng date
2	123456		1/1/24					123456		y	Yes	45292
3	123407		No Match					123407		n		45293
4	123358		No Match					123358		y		45294
5	123309		No Match					123309		n	Yes	45295
6	123260		No Match					123260		y	Yes
7	123211		1/1/23					123211		Y	Yes	1/1/23
8	123162		No Match					123162		y
9	123113		No Match					123113		n	Yes
10	123064		No Match					123064		y	Yes
11	123015		No Match					123015		n
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=FILTER($L$2:$L$11,($J$2:$J$11="Y")*($K$2:$K$11="YES")*($L$2:$L$11<>"")*($H$2:$H$11=A2),"No Match")

If more than 1 entry for that ID
how about
=TEXTJOIN(" , ",,TEXT(FILTER($L$2:$L$11,($J$2:$J$11="Y")*($K$2:$K$11="YES")*($L$2:$L$11<>"")*($H$2:$H$11=A2),"No Match"),"DD/MM/YY"))

Book19
	A	B	C	D	E	F	G	H	I	J	K	L
1	OUT FOR ENGST		TO ENG?					OUT FOR ENGST		complete	ENGST	sent to eng date
2	123456		01/01/24 , 26/04/24					123456		y	Yes	45292
3	123407		No Match					123407		n		45293
4	123358		No Match					123358		y		45294
5	123309		No Match					123309		n	Yes	45295
6	123260		No Match					123260		y	Yes
7	123211		01/01/23					123211		Y	Yes	1/1/23
8	123162		No Match					123162		y
9	123113		No Match					123113		n	Yes
10	123064		No Match					123064		y	Yes
11	123015		No Match					123456		y	yes	4/26/24
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=TEXTJOIN(" , ",,TEXT(FILTER($L$2:$L$11,($J$2:$J$11="Y")*($K$2:$K$11="YES")*($L$2:$L$11<>"")*($H$2:$H$11=A2),"No Match"),"DD/MM/YY"))

maybe a dropbox file helps
only on share for a few days

extract based on criteria YES Y ID OUTFORENGST-ETAF.xlsx

Shared with Dropbox

www.dropbox.com