Lookup Value in Range/Array and Return Column Header Value

I'm trying to make table tents for a banquet and need a formula that
will return the table number for the specific guest.

Excel Layout:

Table #: 1 2 3
Joe Mary Adam
Mike Erin Steve
Ann Ken Jill

Lookup Erin Returns table 2
Lookup Adam Returns table 3
Lookup Ann Returns table 1
etc.

I've tried v and h lookups but those can't use a range/array of values
(the names of the guests). I'd prefer a function but willing to use VBA
if need be. I'm guessing I need a match or similiar function but can't
seem to figure it out.

Any help would be great.

Try something like this:

With a table in information in B2:D5

Table 1 Table 2 Table 3
Joe Mary Adam
Mike Erin Steve
Ann Ken Jill

A1: Erin
B1: =INDEX(B2:D2,SUMPRODUCT((B3:D5=A1)*COLUMN(B3:D5))-COLUMN(A:A))
In this example, B1 returns "Table 2"

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"[email protected]" wrote:

> I'm trying to make table tents for a banquet and need a formula that
> will return the table number for the specific guest.
>
> Excel Layout:
>
> Table #: 1 2 3
> Joe Mary Adam
> Mike Erin Steve
> Ann Ken Jill
>
> Lookup Erin Returns table 2
> Lookup Adam Returns table 3
> Lookup Ann Returns table 1
> etc.
>
> I've tried v and h lookups but those can't use a range/array of values
> (the names of the guests). I'd prefer a function but willing to use VBA
> if need be. I'm guessing I need a match or similiar function but can't
> seem to figure it out.
>
> Any help would be great.
>
>

Thanks so much Ron, That worked beautifully. If you have time could
you explain how it works, particulary the Sumproduct part. I would have
never come up with this.

Again thanks...you're the man!


Ron Coderre wrote:
> Try something like this:
>
> With a table in information in B2:D5
>
> Table 1 Table 2 Table 3
> Joe Mary Adam
> Mike Erin Steve
> Ann Ken Jill
>
> A1: Erin
> B1: =INDEX(B2:D2,SUMPRODUCT((B3:D5=A1)*COLUMN(B3:D5))-COLUMN(A:A))
> In this example, B1 returns "Table 2"
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "[email protected]" wrote:
>
> > I'm trying to make table tents for a banquet and need a formula that
> > will return the table number for the specific guest.
> >
> > Excel Layout:
> >
> > Table #: 1 2 3
> > Joe Mary Adam
> > Mike Erin Steve
> > Ann Ken Jill
> >
> > Lookup Erin Returns table 2
> > Lookup Adam Returns table 3
> > Lookup Ann Returns table 1
> > etc.
> >
> > I've tried v and h lookups but those can't use a range/array of values
> > (the names of the guests). I'd prefer a function but willing to use VBA
> > if need be. I'm guessing I need a match or similiar function but can't
> > seem to figure it out.
> >
> > Any help would be great.
> >
> >

OK...here you go:

Regarding: SUMPRODUCT((B3:D5=A1)*COLUMN(B3:D5))
The (B3:D5=A1) section tests every cell in B3:D5 for a match to A1.
Matches return TRUE
Non-matchs return FALSE

When TRUE/FALSE statements are multiplied by a number, they convert to 1 and
0, respectively. So (B3:D5=A1) returns a list of 1's and 0's.

Regarding: COLUMN(B3:D5)
That part of the formula returns the column number (Col_B: 2, Col_C:3, etc)
for each referenced cell.

When multiplied together, each cell matching A1 equates to a 1 and that
value is multipied times the corresponding column number. Since, in your
example, there can only be one match....all other cells return zero. The
SUMPRODUCT function returns the sum of the values.....a whole bunch of
zeros..and one column number.

Since our range begins in Col_B, equating to 2, we must subtract 1 from all
column number values so we can properly refernce the table headings.

For an extensive explanation of the SUMPRODUCT function see this website:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"[email protected]" wrote:

> Thanks so much Ron, That worked beautifully. If you have time could
> you explain how it works, particulary the Sumproduct part. I would have
> never come up with this.
>
> Again thanks...you're the man!
>
>
> Ron Coderre wrote:
> > Try something like this:
> >
> > With a table in information in B2:D5
> >
> > Table 1 Table 2 Table 3
> > Joe Mary Adam
> > Mike Erin Steve
> > Ann Ken Jill
> >
> > A1: Erin
> > B1: =INDEX(B2:D2,SUMPRODUCT((B3:D5=A1)*COLUMN(B3:D5))-COLUMN(A:A))
> > In this example, B1 returns "Table 2"
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "[email protected]" wrote:
> >
> > > I'm trying to make table tents for a banquet and need a formula that
> > > will return the table number for the specific guest.
> > >
> > > Excel Layout:
> > >
> > > Table #: 1 2 3
> > > Joe Mary Adam
> > > Mike Erin Steve
> > > Ann Ken Jill
> > >
> > > Lookup Erin Returns table 2
> > > Lookup Adam Returns table 3
> > > Lookup Ann Returns table 1
> > > etc.
> > >
> > > I've tried v and h lookups but those can't use a range/array of values
> > > (the names of the guests). I'd prefer a function but willing to use VBA
> > > if need be. I'm guessing I need a match or similiar function but can't
> > > seem to figure it out.
> > >
> > > Any help would be great.
> > >
> > >
>
>

would anybody happen to know how to do this if the table were transposed? eg

table 1 kevin, bill, james
table 2 mary, nancy, susan
table 3 brian, janice, bruce

id like to be able to have a formula where if i type "bruce" it will return table 3.

tried to apply the same logic in the above post but doesnt seem to work when the logic is applied to rows instead of columns. thank you in advance!

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.