how to get sequence values of criteria by using VLOOKUP

[vijay.vizzu]

Dear All,


May be i am cross posting, but i didn't get my requirement by searching, so now i am posting this question. Please apologize my cross posting if any.

My problem is, i have a table assume that A1:F100, i am using vlookup function in other sheet to get my answers, but as all of you know that, vlookup only returns first occurrence of the criteria in the range. can i get the values of the criteria in row wise?

This is the table:

Name Class Grade Marks

Vijay 9 A 54

Kumar 10 B 43

Naveen 11 C 21

Kumar 12 D 45

Suresh 8 E 91

Kumar 7 A 29


Now i want the results like below

Name Marks Marks Marks

Vijay 54

Kumar 43 45 29

Naveen 21

Suresh 91


So please suggest which function will be suitable to get this?


Thanks

Vijay

 

[shrivallabha]

Try this ARRAY formula [They need to be CTRL + SHIFT + ENTER -ed and not just ENTER).


I have assumed that:

Your data is on Sheet1 Range A1:D7 and you are trying summarize info on Sheet2 then in Sheet2 RangeB2 enter this formula:

=IFERROR(INDEX(Sheet1!$A$1:$D$7,SMALL(IF(Sheet1!$A$1:$A$7=$A2,ROW($A$1:$A$7)),COLUMNS($B2:B2)),4),"")

Then copy this formula DOWN and ACROSS.

 

[lashaunda852]

Great post!Thank you for all the things learned from here and congratulations for the good work.

For more fake watches details, come to my blog and copy watches.