Lookup Value in Range/Array and Return Column Header Value
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
" 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.
|