HLookup to link data across several worksheets

  • Thread starter Thread starter librarynut
  • Start date Start date
L

librarynut

I have a workbook with 3 specific worksheets for grouping student
together by cabin, table, and study group. I have a master workshee
of student names. I need a formula to lookup the student nam
(MasterList, col A) in the individual worksheets and return the valu
of row1 in whatever column the name is found in. Can I do this wit
the Hlookup function?
An example of the function I tried is:

=HLOOKUP(A2,Cabins!F3:I24,1,FALSE)

The worksheets aren't sorted in any order. The MasterList of studen
names will be sorted by teacher, then alphabetical order.

I am attaching a copy of the workbook
 
Hi
not quite sure what you're trying to do as you said that your student
names are in column A but you want the entry from row1 for the column
in which the name is found. As they are in column A this is always cell
A1 -> you probably meant something else.

Best way would be to post some example rows (plain text - no
attachments please) an state what your expected result is
 
Here is an example of the data i am using:
Sheet1 (MasterList)

A ; B
Student Names; Cabin
Joe Smith; {function}
Jane Doe; {function}
Shania Twain; {function}
Fred White; {function}
Bill Cosby; {function}
Jennifer Jones; {function}

Sheet2 (Cabins)
A; B; C
Appaloosas; Longhorns; McManus
Joe Smith; Bill Cosby; Fred White
Jennifer Jones; Shania Twain; Jane Doe


Where the function looks at Sheet2, locates the name of the student
and returns the header of that column it is found in, such as:
Joe Smith; Appaloosas
Jane Doe; McManus

I hope this is clearer, and I sure do appreciate the help. M
attendance secretary is breathlessly waiting to see if I can find th
answer
 
Hi
try the following formula in B2 (for Joe smith):
=INDEX('sheet2'!$A$1:$E$1,1,SUMPRODUCT(MAX(('sheet2'!$A$1:$E$100=A1)*(C
OLUMN('sheet2'!$A$1:$E$100)))))
and copy down

Note: The student names have to be unique to make this work!
 
=INDEX(Cabins!$A$1:$H$1,1,SUMPRODUCT(MAX((Cabins!$A$1:$H$200=A2)*(COLUMN(Cabins!$A$1:$H$200)))))

is the formula I am using, thanks to Frank. However, if the name on th
master sheet (A2) is not found on the Cabins sheet, i would like it t
return an "error!" message. Is this possible?

Unfortunately, i don't understand enough of how the formula works to d
it myself.

Thanks, Juli
 
Hi
try
=IF(SUMPRODUCT(MAX((Cabins!$A$1:$H$200=A2)*(COLUMN(Cabins!$A$1:$H$200))
))=0,"error",INDEX(Cabins!$A$1:$H$1,1,SUMPRODUCT(MAX((Cabins!$A$1:$H$20
0=A2)*(COLUMN(Cabins!$A$1:$H$200))))))
 
Frank, this is fantastic! Thank you for your help. I am definitely
heroine this week at work thanks to your (timely)help.

--Juli
 
Back
Top