Get Cell value from adjacent cell if it has specific number or Text - 1 in given case

[analystbank]

Sample Excel workbook is attached (version 1997-2000)

I have Table of Students in Range A1:L209 (this could extend to 250 or 300 or so, but currently 209 only)

Column A has Student name

Column B to J has some other values retrieved through formulas from other worksheet within same workbook. (perhaps not relevant for this query)

Column K has header 'Passed' for which value 1 is assigned through formula against respective student who have passed
Column L has header 'Failed' for which value 1 is assigned through formula against respective student who have failed

I want a small tabulation for both categories of students (all those) who have passed and failed separately


All Those who have passed - Starting from Cell O28
All Those who have failed - Starting from Cell O47


Column P to S i will populate once i get the name using Index/Match Concept.


I have filled the O28 and O47 table just to show how it should look like for understanding purpose.

[AliGW]

In O28:

=IFERROR(INDEX(A:A,SMALL(IF($K$2:$K$209=1,ROW($A$2:$A$209)),ROWS($A$1:A1))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

In O47:

=IFERROR(INDEX(A:A,SMALL(IF($L$2:$L$209=1,ROW($A$2:$A$209)),ROWS($A$1:A1))),"")

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy down.

As your data grows, you will need to extend the ranges, so consider making your data range into a table and using field values instead (see attached), because then your data can grow without affecting your formulae. You would then need formulae like this:

=IFERROR(INDEX(StudentSuccess[Student],SMALL(IF(StudentSuccess[Passed]=1,ROW(StudentSuccess[Student])),ROWS($A$1:A1))-1),"")

=IFERROR(INDEX(StudentSuccess[Student],SMALL(IF(StudentSuccess[Failed]=1,ROW(StudentSuccess[Student])),ROWS($A$1:A1))-1),"")

[maras_mak]

Hi,
Cell 'O28' formula then dragg down.

Please Login or Register  to view this content.

OR older version of Excel

Please Login or Register  to view this content.

Cell 'O47' formula and dragg down

Please Login or Register  to view this content.

OR older version of Excel

Please Login or Register  to view this content.

Regards.

[analystbank]

@Thanks a ton, AliGW Esq. (Madam), for offering solution and flexibility for future purpose.

I will try to understand what each subset of formula does the work and brings out the desired result.

@maras_mark, your is also splendid to see that max row number could be defined which can accommodate changing data (population) requirement .


@Maras_mark, Sir if you can explain what your subset of formula does, will be helpful, or i will google and see respective excel function to decipher what it mean and how it brings result.

Thanks all

[AliGW]

Esquire is a word used with men, never women, but thanks for the comments - glad to have helped.

[analystbank]

Esquire is a word used with men, never women, but thanks for the comments - glad to have helped.

apology for that, this is what happens when less is known. Thanks for correcting.

[AliGW]

Just a titbit of information!

Thanks for the rep.