Index Match array formula need to pull names whose adjacent cell matches given criteria

1. Index Match array formula need to pull names whose adjacent cell matches given criteria

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

2. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

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>

3. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

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.

4. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

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>

5. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

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.

6. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

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?

7. Re: Index Match array formula need to pull names whose adjacent cell matches given criteri

Cheers

You are welcome bkwins.

please mark this thread as [SOLVED].. thanks.

Regards,
DILIPandey
<click on below * if this helps>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1