Hello Team!
I am trying to do an index formula where it looks up an array based on a value I select in the drop down "c2". The value would be considered “C2” for this purpose on the first tab. Now when that is selected, I want to show the providers names that only have that value associated with them in columns f,g,h. I would repeat the formula for the next grouping of columns for each date. Each column is a shift and the 3 shifts are for one date.
Here is the formula I have so far:
=IFERROR(INDEX('Report Sheet'!$A$4:$AP$601,SMALL(IF('Report Sheet'!F$5:F$601=$C$2,ROW('Report Sheet'!F$5:F$601)),ROW(1:1))-3,1),"")
This does great but will only give me the providers name if that value is in column F only. I want it to give me the providers name if the “c2” value is in any of the 3 columns for that row(F,G,H).
The logic: If I enter NW into “c2”, I want to look up in my 2nd tab and see if NW is in one of those shifts for June 6. If it is, give me that providers name. and then repeat to find show the second providers name that has the same arguments.
Now if we want to really make it fancy, I would love for it to label the shift after the provider name based on where the “c2” value is found.
IE:
Doe, John AM,PM
Smith, Jack EVE PM
Thanks you guys! I attached an example file.
Bookmarks