Need to find all instances of value and return any non-blank cell from another sheet?

Tanya.Doreine

Hello Everyone,

I'm stumped and need some assistance. What I am trying to do is look for the value in the Cable Number column (Cable Number]@row) on the master sheet in the Cable Number Column on the Update Tracker (Update Tracker Cable}). There will be multiple times this value is in this column but some dates will be blank.

I then need the non-blank date pulled from Cable Pull Start Date ({Update Tracker CPSD}) on the Update Tracker to the Cable Pull Start Date on the Master.

This is the formula I am trying but it keeps returning blank values:

=INDEX({Update Tracker CPSD}, MATCH([Cable Number]@row, {Update Tracker Cable}, 0), 1)

SSFeatures

Hi,

I just wrote this formula for you and I think it will work:

= IFERROR(
	INDEX(
	    COLLECT(
	        {Update Tracker CPSD}, 
	        {Update Tracker CPSD},
	        NOT(ISBLANK(@cell)),
	        {Update Tracker Cable}, 
	        @cell = [Cable Number]@row
	    ), 
	    1
	),
	""
)

This is how the logic works.

1. Begin collecting data from {Update Tracker CPSD}.
2. Criteria: Make sure that the cell is not blank.
3. Criteria: For each row, look at the corresponding cell in {Update Tracker Cable}, make sure that it is equal to the current row's [Cable Number].
4. INDEX: Grab the first value that we found from COLLECT.
5. If COLLECT didn't have any data, then it will throw an error. This means that the Cable number did not have a date, so let's just set the cell to "".

Let me know if you have any questions!

SSFeatures