Searching a range with multiple criteria

[Ally]

Hi

I've done searches on this and am guessing I probably need Index and Match but have gotten completely confused with the answers that are there and can't see if any of them apply to my query.

I have a sheet with Primary Diagnosis, First Secondary Diagnosis, Second Secondary Diagnosis, up until 6th.

I have a list of diagnoses and want my formula to return the first one of any matches that appear in the range.

Sheet 2 is the list of values that I want to use to lookup from.

Sheet 1 is the main data, with column C where I want to put my formula.

What I want this to do is return in each row, the first instance any of the values from the lookup match, ie in the first one it will be I209. Not every row has more than one instance.

I have played with Lookup, HLOOKUP, Match and Index and can't figure it out.

Any help would be really greatly appreciated.

Thank you

Ally

 

[Ally]

I got a response from another forum in case anyone is interested:
=INDIRECT(ADDRESS(ROW(),MIN(IF(D2:J2=Sheet2!$A$1:$A$10,COLUMN(D2:J2),""))))

Thanks