how to use vlookup for multiples rows

[emmr]

Dear All,
if multiple rows required through vlookup then how i can do this, attached file for your convenience.

NB: if the title is not matched with my query then please excuse me.

TIA

[Sam Capricci]

this will work for you but the way you set up the output box with one ID for each box, you'll have to keep changing the reference cell...
=LOOKUP(2,1/($A$2:$A$10=$F$2)/($B$2:$B$10=H2),$C$2:$C$10)
so I locked in F2 so it stays pointed at that cell but when you use it for nabi you'll need to change it to ...
=LOOKUP(2,1/($A$2:$A$10=$F$7)/($B$2:$B$10=H2),$C$2:$C$10)
unless you put nas also in F3 and F4 and nabi in F8 and F9 etc, then you can drop the absolute reference to $F$2 and instead use just F2.
hope that makes sense.

[JohnTopley]

Try

=IFERROR(INDEX(B$2:B$100,AGGREGATE(15,6,ROW($A$1:$A$100)/($A$2:$A$100=$F$2),ROWS($1:1))),"")

copy across to C and down to last entry for selected ID

As your output stands, you will need to change highlighted value for each ID block. Not very practical if you have large number of IDs.

[emmr]

Sam Capricci thanks for your reply, however i didn't get you, could you please show in the excel file please .

[Sam Capricci]

ok, see the difference in the formula with absolute reference in the formula in cell J2 and copied down vs the formula in J7 copied down.
I left nas in only F2 while putting nabi in F7 and F8 etc. John also referred to the issue in his response.

[JohnTopley]

See attached which uses relative references as Sam's suggestion so you simply copy/paste to each ID block

=IFERROR(INDEX(B$2:B$10,AGGREGATE(15,6,ROW($A$1:$A$100)/($A$2:$A$10=$F2),COUNTIF($F$2:$F2,$F2))),"")

[emmr]

thanks a lot JohnTopley & Sam Capricci, its working

[JohnTopley]

... and why are doing this anyway ???

[Sam Capricci]

You're welcome, and don't forget, clicking on * Add Reputation below the post of any or all of those who've stopped by to help is always welcome, it is how we advance on this forum.