Hello and sorry upfront, I'm sure this has been asked and answered lots of times but I can not make this work.

Here is what I need to do:

the info on Sheet1 has a bunch of names in Column V with info behind

On Sheet 2 I need to lookup each match in Column V and return all the info for each match to the column

Sheet1

[TABLE="width: 500"]

<tbody>[TR]

[TD][/TD]

[TD]V[/TD]

[TD]Z[/TD]

[TD]AA[/TD]

[TD]AB[/TD]

[/TR]

[TR]

[TD]1[/TD]

[TD][/TD]

[TD]Text[/TD]

[TD]Info[/TD]

[TD]Note[/TD]

[/TR]

[TR]

[TD]2[/TD]

[TD]Brendan[/TD]

[TD]Text 1[/TD]

[TD]Info 1[/TD]

[TD]Note 1[/TD]

[/TR]

[TR]

[TD]3[/TD]

[TD]Andy[/TD]

[TD]Text 2[/TD]

[TD]Info 2[/TD]

[TD]Note 2[/TD]

[/TR]

[TR]

[TD]4[/TD]

[TD]Brendan[/TD]

[TD]Text 3[/TD]

[TD]Info 3[/TD]

[TD]Note 3[/TD]

[/TR]

</tbody>[/TABLE]

Sheet2

[TABLE="width: 500"]

<tbody>[TR]

[TD][/TD]

[TD]A[/TD]

[TD]B[/TD]

[TD]C[/TD]

[TD]D[/TD]

[/TR]

[TR]

[TD]1[/TD]

[TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]2[/TD]

[TD]Text[/TD]

[TD]Lookup A1 & return: Text 1[/TD]

[TD]Lookup A1 & return: Text 3[/TD]

[TD][/TD]

[/TR]

[TR]

[TD]3[/TD]

[TD]Info[/TD]

[TD]Lookup A1 & return: Info 1[/TD]

[TD]Lookup A1 & return: Info 3[/TD]

[TD][/TD]

[/TR]

[TR]

[TD]4[/TD]

[TD]Note[/TD]

[TD]Lookup A1 & return: Note 1[/TD]

[TD]Lookup A1 & return: Note 3[/TD]

[TD][/TD]

[/TR]

</tbody>[/TABLE]

Not sure if this makes sense (hope so ) but can someone tell me how to do that?

Here is how I return the first row only =VLOOKUP($B$1,responses!$V:$AK,3,FALSE)

and here is what I have tried but it doesn't work and I can not figure out why not

=INDEX(responses!$V3:$AK100,SMALL(IF(responses!$V$3:V100=$B$1,ROW(responses!$V$3:$V$100)-1),COLUMNS($D2:D2)),2)

Thanks

Sabine

PS using Excel 7

Here is what I need to do:

the info on Sheet1 has a bunch of names in Column V with info behind

On Sheet 2 I need to lookup each match in Column V and return all the info for each match to the column

Sheet1

[TABLE="width: 500"]

<tbody>[TR]

[TD][/TD]

[TD]V[/TD]

[TD]Z[/TD]

[TD]AA[/TD]

[TD]AB[/TD]

[/TR]

[TR]

[TD]1[/TD]

[TD][/TD]

[TD]Text[/TD]

[TD]Info[/TD]

[TD]Note[/TD]

[/TR]

[TR]

[TD]2[/TD]

[TD]Brendan[/TD]

[TD]Text 1[/TD]

[TD]Info 1[/TD]

[TD]Note 1[/TD]

[/TR]

[TR]

[TD]3[/TD]

[TD]Andy[/TD]

[TD]Text 2[/TD]

[TD]Info 2[/TD]

[TD]Note 2[/TD]

[/TR]

[TR]

[TD]4[/TD]

[TD]Brendan[/TD]

[TD]Text 3[/TD]

[TD]Info 3[/TD]

[TD]Note 3[/TD]

[/TR]

</tbody>[/TABLE]

Sheet2

[TABLE="width: 500"]

<tbody>[TR]

[TD][/TD]

[TD]A[/TD]

[TD]B[/TD]

[TD]C[/TD]

[TD]D[/TD]

[/TR]

[TR]

[TD]1[/TD]

[TD]

**Brendan**[/TD][TD][/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[TR]

[TD]2[/TD]

[TD]Text[/TD]

[TD]Lookup A1 & return: Text 1[/TD]

[TD]Lookup A1 & return: Text 3[/TD]

[TD][/TD]

[/TR]

[TR]

[TD]3[/TD]

[TD]Info[/TD]

[TD]Lookup A1 & return: Info 1[/TD]

[TD]Lookup A1 & return: Info 3[/TD]

[TD][/TD]

[/TR]

[TR]

[TD]4[/TD]

[TD]Note[/TD]

[TD]Lookup A1 & return: Note 1[/TD]

[TD]Lookup A1 & return: Note 3[/TD]

[TD][/TD]

[/TR]

</tbody>[/TABLE]

Not sure if this makes sense (hope so ) but can someone tell me how to do that?

Here is how I return the first row only =VLOOKUP($B$1,responses!$V:$AK,3,FALSE)

and here is what I have tried but it doesn't work and I can not figure out why not

=INDEX(responses!$V3:$AK100,SMALL(IF(responses!$V$3:V100=$B$1,ROW(responses!$V$3:$V$100)-1),COLUMNS($D2:D2)),2)

Thanks

Sabine

PS using Excel 7

Last edited: