Eliminate N/A from spreadsheet and open range of search

Thank you NBVC!

The answer below worked perfectly for me.

Thank you for the help!

Anthony

To hide the N/A you could use an error trap:

=IF(ISNA(INDEX(schedule!$A:$AA,MATCH(A3,schedule!$K:$K,0),MATCH(G$1,schedule!$A$1:$AA$1,0))),"",INDE X(schedule!$A:$AA,MATCH(A3,schedule!$K:$K,0),MATCH(G$1,schedule!$A$1:$AA$1,0)))

To retrieve multiple dates in the same cell, you need a User Defined Function.. and you would need to use limited ranges instead of whole columns... and also efficiency would be reduced.

You can try this udf:


Code:
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End FunctionThen apply formula in G3 of Ad sheet:


Code:
=SUBSTITUTE(TRIM(MCONCAT(IF(schedule!$K$3:$K$64=A3,TEXT(schedule!$A$3:$A$64,"d-mmm")," ")))," ",", ")which you must confirme with CTRL+SHIFT+ENTER and not just ENTER and copy down... make adjustments as necessary and reconfirm with CSE before copying down and/or across.

I have attached my current spreadsheet.
This includes a schedule on tab 1 and three different pages Ad, Pr, and Po that contain a list of materials to search the schedule for.

1. Currently if the search doesnt find the value on the schedule page it returns n/a.
I understand why, but would like to hide/eliminate that.

2. Right now the search will look through the schedule to find a match. Once that match is found it stops.
It is possible that we will make that material more than just that one time and if so I would like the search to return EVERY date that product is being made.

Any help is greatly appreciated.

Anthony

To hide the N/A you could use an error trap:

=IF(ISNA(INDEX(schedule!$A:$AA,MATCH(A3,schedule!$K:$K,0),MATCH(G$1,schedule!$A$1:$AA$1,0))),"",INDEX(schedule!$A:$AA,MATCH(A3,schedule!$K:$K,0),MATCH(G$1,schedule!$A$1:$AA$1,0)))

To retrieve multiple dates in the same cell, you need a User Defined Function.. and you would need to use limited ranges instead of whole columns... and also efficiency would be reduced.

You can try this udf:

Please Login or Register  to view this content.

Then apply formula in G3 of Ad sheet:

Please Login or Register  to view this content.

which you must confirme with CTRL+SHIFT+ENTER and not just ENTER and copy down... make adjustments as necessary and reconfirm with CSE before copying down and/or across.