Formula to Match Value of Range and Return Non Blank Cells of Corresponding Column

I am trying to find a formula that will allow me to enter a company name into a single cell on a worksheet ($A$1). (See pages Named MEC and Big Elk. Match value of $A$1 to one of the values in Column I of the Test EMPL Tracker page. Then return a list of the non-blank values in the column (Column D or E) representing the value from Column I (Company Name) to the Worksheet where the Company Name originated from into Column A.

Array enter (Ctrl+Shift+Enter) in A6 in either MEC or Big Elk tab and copy down:

Formula:

=IFERROR(INDEX(OFFSET('Test EMPL Tracker'!$C$3,,MATCH($A$1,'Test EMPL Tracker'!$D$2:$G$2,0),1000,1),MATCH(1,(COUNTIF(A$5:A5,OFFSET('Test EMPL Tracker'!$C$3,,MATCH($A$1,'Test EMPL Tracker'!$D$2:$G$2,0),1000,1))=0)*(OFFSET('Test EMPL Tracker'!$C$3,,MATCH($A$1,'Test EMPL Tracker'!$D$2:$G$2,0),1000,1)<>""),0)),"")

The value 1000 in formula refers to estimated number of rows in your data. Adjust it accordingly.

Good luck!

Estevaoba this is exactly what I needed. Thanks a million for the help !

You're welcome.