Find a value in a table and return the cell or column reference
Try something like this:
With
your posted data in cells A1:G7
AND assuming there are NO duplicate values in that range.
I1: (any value from the list)
J1: =INDEX($A$1:$G$1,1,SUMPRODUCT(($A$2:$G$7=I1)*COLUM N($A$1:$G$1)))
If I1: 1160009
the formula in J1 returns Group E
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"jgrout" wrote:
Please help! I need a formula that will allow me to search across multiple
rows and columns and find a match and then return the cell reference or
column reference of the match. I can't just use vlookup or hlookup, because
they only search the first column or row. Below is an example of the data I
will be searching. Each month I get a list of names and a number associated
with that name, and I need a formula that will identify which group that
number belongs to by searching the data below.
GROUP A GROUP B GROUP C GROUP D GROUP E GROUP F GROUP G
00080001 01160001 01160023 00700001 01160007 01160010 01160020
00690001 01160002 01160008 01160018
00690002 01160003 01160009
00690003 01160004 01160011
00690004 01160005 01160012
01500001 01160006 01160013
|