What function would I use to reference a different cell in a row based on what is in another cell?

Jordan Myers · 07/12/21

I recently asked a question similar to this and got an answer. However, I am having to go about it a different way, this is all on me, and now I am up against another wall.

I am trying to reference a cell based on what is in the same row but a different cell. I am not doing any calculations so I cannot use @cell.

Posted below are the two sheets I am trying to link. The first picture is the column I reference for finding crew names. Based on what is there, the function references the row number of that column through a helper column. My issue is that it won't recognize a name in a list. The solution given to me previously was to use an @cell in a HAS function. I am no longer using a SUMIF formula to collect the proper row number so I can't use the @cell any more.

My new function is to use a MIN(COLLECT( function. This works well because I can set up a drop down menu for multiple jobs assigned to a crew member and affect it by using the n-th limiter in the MIN function. This works as long as there is only one name to look at.

Could I somehow use the HAS function to recognize a name in a list of names? I have tried just using a a cell reference for the criteria as well as a direct text to look for in the COLLECT function and it does not work.

Thank you