Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
I need a function that will tell me, for each record on Sheet1, the cell reference on Sheet2 where that Subject appears.
Does such a function exist?
| Check out our Excel Resources | ||||
 |
 |
|||
Which range is it that houses your table? Which column is it that specifically houses the various subjects under the lable Subject?
Which range is it that houses the various subjects?
The answer is yes I guess.
Aladin
Juan Pablo G.
Sheet2:
Columns B through Q have a header on Row 1 which are the names of the 16 Groups that the "Subjects" in Sheet1 can belong to. Each of these columns on Sheet2 has varying lengths of data, depending on how many "Subjects" are in each group.
What ULTIMATELY want to do is this: For each Subject in Column D of Sheet1, I want to know the name of the Group that the Subject belongs in (from Sheet2, row 1 where the headers of columns B through Q are.)
Some kind of Lookup or Index/Match formula is required, but first I need to know in which Column in Sheet2 I can find each Subject.
======
For illustration's sake, lets say the Subjects are Apples, Oranges, Cashews, Pecans, Bananas, and Bubble Gum. The Groups are Fruits, Nuts, and Candy. Each of the Subject fits into a group - the Groups are pre-determined.
Juan Pablo G.
Column B | Column C | Column D
(Header) Fruits Nuts Candy
2 Apples Cashews Bubble Gum
3 Bananas Pecans
4 Oranges
=INDEX(Sheet2!$B$1:$D$1,(ISNUMBER(MATCH(D2,Sheet2!$B$2:$B$20,0))*1)+(ISNUMBER(MATCH(D2,Sheet2!$C$2:$C$20,0))*2)+(ISNUMBER(MATCH(D2,Sheet2!$D$2:$D$20,0))*3))
I worked with the three groups, since you need to work with 16 i think it's better to create 2 formulas (One for the ISNUMBER(MATCH(...)) things in column F for example and the other one (INDEX(Sheet2!...,F2) in Column E.
Juan Pablo G.
How about entering in Sheet1 in the cell next to the first Subject
=INDEX(Sheet2!$B$1:$Q$1,SUMPRODUCT((ISNUMBER(SEARCH(D2,Sheet2!$B$2:$Q$20)))*(COLUMN(Sheet2!$B$2:$Q$20)))-1)
where I assumed the same range as you in Sheet2 but applying INDEX to the top row in Sheet2 which houses the Groups as I understood it.
Caveat. This formula assumes that a subject is not categorized in more than a single category.
Regards,
Aladin
=======
Juan Pablo G.
{=INDIRECT("Sheet2!"&ADDRESS(1,(MAX(IF($B$2:$Q$20=D2,COLUMN($B$2:$Q$20),0))))}
Assuming your Subject titles are in row 1 Sheet2.
{=INDIRECT("Sheet2!"&ADDRESS(1,(MAX(IF(Sheet2!$B$2:$Q$20=Sheet1!D2,COLUMN(Sheet2!$B$2:$Q$20),0))))}