Find a value in a table and return the cell or column reference
 

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

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

Find a value in a table and return the cell or column referenc
 

Works beautifully! Thanks for your quick reply.

"Ron Coderre" wrote:

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

Find a value in a table and return the cell or column referenc
 

could u please explain how the formula works??