index function

[sna]

Hi Dear

I need to lookup value and return value from adjacent cell but only unique

Note: need only formula

I attached a template

Best Wishes,

[norkaz]

..

It could be possible and properly to use the formulas that place E2:G5 as attached.

Try this, if you need another way.


I2
=IFERROR(INDEX($A$1:$A$16,AGGREGATE(15,6,ROW($A$1:$A$16)/($A$1:$A$16<>""),ROW(A1))),"")

Copy down.

J2
=IFERROR(INDEX($B$1:$B$16,AGGREGATE(15,6,ROW($B$1:$B$16)/(LOOKUP(ROW($A$1:$A$16),ROW($A$1:$A$16)/($A$1:$A$16<>""))=MATCH($I2,$A$1:$A$16,0)/($B$1:$B$16<>"")),COLUMNS($J$2:J2))),"")

Copy down and to the right hand side.


Norkaz

[sna]

Hi Norkaz,

In J2 not what I need .
In should return unique subject by student name like below
Sharmin English Math
Mahi English
Mahiha Math English Physique
Arifa Math English French


Best Wishes

[norkaz]

Hi Guy,

Please give me any info for more clarify about your points.

If my understand is correct, refer to your first attached you don't need the formulas E2:G5 but you need only "ONE FORMULA" that place D2 and then copy to the right hand side and down , then the results would be as appeared as attached, right?

Norkaz

[norkaz]

D2

=IFERROR(INDEX(INDEX($A$1:$B$16,0,MIN(COLUMNS($D$2:D2),2)),IF(AGGREGATE(15,6,ROW($A$1:$A$16)/($A$1:$A$16<>""),ROW(A1))+COLUMNS($D$1:D1)-1>IFERROR(AGGREGATE(15,6,ROW($A$1:$A$16)/($A$1:$A$16<>""),ROW(D2))-1,AGGREGATE(14,6,ROW($A$1:$A$16)/($B$1:$B$16<>""),1)),"",AGGREGATE(15,6,ROW($A$1:$A$16)/($A$1:$A$16<>""),ROW(D1))+COLUMNS($D$2:D2)-1)),"")&""

Copy to right hand side and down.

Norkaz

[sna]

Dear Norkaz

I mean that every subject come from student name is unique.(unique subject)

Like student name Mahi is subject to return is only English once time not three times.


Best Wishes

[norkaz]

E2

=IFERROR(INDEX($B$1:$B$16,AGGREGATE(15,6,INDEX(ROW($B$1:$B$16)/(MATCH($D2&$B$1:$B$16,LOOKUP(ROW($A$1:$A$16),ROW($A$1:$A$16)/($A$1:$A$16<>""),$A$1:$A$16)&$B$1:$B$160,0)=ROW($A$1:$A$16)/($B$1:$B$16<>"")),0),COLUMNS($D$2:D2))),"")

Ctrl + Shift + Enter

Norkaz

[sna]

Thank you it's working now

[norkaz]

Dear Norkaz

I mean that every subject come from student name is unique.(unique subject)

Like student name Mahi is subject to return is only English once time not three times.


Best Wishes

Hi friend

A common problem.

Misunderstandings happen all the time, especially when communication is not clear. And can cost too much time.

One solution that works for many people is just only place the text sample (no need with the formula(s)) ,probably place 2-3 simply answers text values
" WITH THE COLOR HIGHLIGHT" in the template attached would be helpful.

This way might be able to help us save our time together and communicate clearly to find out the right solutions related to the Excel questions from the forum members -- and give you more helpful answer.

A sample as Excel attached.

I'd be happy to help.

Kind regards,
Norkaz

[logic]

just only place the text sample (no need with the formula(s)) ,probably place 2-3 simply answers text values
" WITH THE COLOR HIGHLIGHT" in the template attached would be helpful.

Strongly agree.

[sna]

Noted thanks