Thread: lookup in multiple columns

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.

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)))