Lookup Value in Range/Array and Return Column Header Value

[michaelfly]

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.

 

[Guest]

Try something like this:

With a table in information in B25

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

A1: Erin
B1: =INDEX(B22,SUMPRODUCT((B35=A1)*COLUMN(B35))-COLUMN(A:A))
In this example, B1 returns "Table 2"

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

XL2002, WinXP

 

[michaelfly]

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!

 

[Guest]

OK...here you go:

Regarding: SUMPRODUCT((B35=A1)*COLUMN(B35))
The (B35=A1) section tests every cell in B35 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 (B35=A1) returns a list of 1's and 0's.

Regarding: COLUMN(B35)
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