I need to find a name in an array

[MCL_Playz]

I'm setting a up a name system and i need to find a certain persons name within an array. I have a name in B2 formatted as text and then in another sheet I have an array A2:F16 filled with a list of those names. I need to find a column number relative to A. I'm attempting to use Match. this is what I have:

=MATCH(B2,Years!A2:F16)

. I'm going to use this in an offset to find the value at the top of that column.

 

[kvsrinivasamurthy]

Try.

=SUMPRODUCT((Sheet2!A2:F10=$B2)*COLUMN(Sheet2!A2:F10))

If heading is there in row 1 and Column header is required.

=INDEX(Sheet2!A1:F1,SUMPRODUCT((Sheet6!A2:F10=$B2)*COLUMN(Sheet6!A2:F10)))

 

[MCL_Playz]

Try.

=SUMPRODUCT((Sheet2!A2:F10=$B2)*COLUMN(Sheet2!A2:F10))

If heading is there in row 1 and Column header is required.

=INDEX(Sheet2!A1:F1,SUMPRODUCT((Sheet6!A2:F10=$B2)*COLUMN(Sheet6!A2:F10)))

Worked perfectly thank you, can you please explain how it works tho

 

[Fluff]

Another option

=TOCOL(IF(Years!A2:F16=B2,Years!A1:F1,1/0),2)

 

[kvsrinivasamurthy]

In SUMPRODUCT formula 1st part searches for the B2 value in array. Second part Gives the Column number.
Finally It gives the required column Number.
Index Formula selects the value in 1st row corresponding to Column number.