LinkBack URL
About LinkBacks
I have a long column of student names and the names of the classes are in the adjacent column what formula will pull all the names for a given class?IndexMatchArray.xls
I have a long column of student names and the names of the classes are in the adjacent column what formula will pull all the names for a given class?IndexMatchArray.xls
Hi bkwins,
You have given 1,2,3,4,5,6 in column A but what will tell excel if you are talking about class 1 or 2 or 3 ? thx
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Hi
Sin you on Excel 2003
Cell B3 =IF(COUNTIF($L$3:$L$30,$B$2)<ROW(A1),"",(INDEX($K$3:$L$30,SMALL(IF($L$3:$L$30=$B$2,ROW($L$3:$L$30)-MIN(ROW($L$3:$L$30))+1),ROW(1:1)),COLUMN(A1))))
Ctrl+Shift+Enter array formula
Please see the file.
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Anyways, below formula will fetch your expected results :-
Formula:
Please Login or Register to view this content.
enter with key combination: ctrl shift enter
see attached:- IndexMatchArray.xls
Regards,
DILIPandey
<click on below * if this helps>
micope and dilipandey, thank you both for your replies! I went with dilipandey's solution because it was easier for me to implement in the original file.
I have a related question though,
If I paste the above formula in Sheet1 but in my workbook this section of the formula ROW($L$1:$L$22) is changed to ROW(Sheet3!$L$1:$L$22) Then if I delete Sheet2 I get ROW(#REF!$L$1:$L$22)
Is there a way to make that ROW reference to a different sheet robust?
Cheers
You are welcome bkwins.
please mark this thread as [SOLVED].. thanks.
Regards,
DILIPandey
<click on below * if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks