Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Value in Range/Array and Return Column Header Value
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. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Value in Range/Array and Return Column Header Value
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 " 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 " 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Dimensional Lookup by column & rows to return score grade | Excel Discussion (Misc queries) | |||
Lookup in one column, and return value from another column | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Column header, if row value is > X | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |