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

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

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

could u please explain how the formula works??