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


"michaelfly@gmail.com" 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.
>
>