Function to tell me the cell address of a value (from another sheet)

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

Care to post a little more details ? that formula doesn't exist "directly" but it's doable.

Juan Pablo G.

More Details:
=======
Sheet1:
Column D contains "Subject" for 7000+ Records, starting on Row 2.

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.

Where is the Predetermined Info ? (Fruits,Nuts, and Candy) ?

Juan Pablo G.

The Predetermined Info (for this example) is in Sheet2 Columns B, C, and D. Example Below (I hope it's readable)

Column B | Column C | Column D
(Header) Fruits Nuts Candy
2 Apples Cashews Bubble Gum
3 Bananas Pecans
4 Oranges

Put this formula in Sheet1 next to first Subject and drag down. If assumed ranges in Sheet2 from row 2 to 20, you can change them.

=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.

Maybe better...

Juan --

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

=======

Thanks... trying to get an array...

but coulnd't figure out how... i knew you could help me with that one.

Juan Pablo G.

Another one - an array formula just for fun

How about something like this: (thru 16 columns)

{=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.

correction (forgot a couple sheet references)

rather:

{=INDIRECT("Sheet2!"&ADDRESS(1,(MAX(IF(Sheet2!$B$2:$Q$20=Sheet1!D2,COLUMN(Sheet2!$B$2:$Q$20),0))))}