lookup in multiple columns

[DonCarlo]

Hi,

I need a function that can lookup in an entire table and return the value in the leftmost column: Here's the example.

Stock Family		Item Codes
ST# 001		7101	7102	7103	7104	7105	7206
ST# 003		7201	7202]	7203	7204	7223	7209
ST# 004		801	802	803]	804	814	829
ST# 005		821	822	823	824	901	902

I need the function to lookup in the items codes and return the respective Stock Family code.
Is this Possible?
Please advise.

[martindwilson]

with value to be looked for in a8 try this array formula confirmed with ctrl+shift+enter
=IF(A8="","",INDEX(A2:A6,MAX((A2:H5=A8)*ROW(A2:H5)-MIN(ROW(A2:H5))+1)))

[DonCarlo]

Thank a Lot Martin!!!

That was very helpful I really appreciate it!!!
Have a nice day!!!

DonCarlo