VLOOKUP vs INDEX?

[sabine]

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

	V	Z	AA	AB
1		Text	Info	Note
2	Brendan	Text 1	Info 1	Note 1
3	Andy	Text 2	Info 2	Note 2
4	Brendan	Text 3	Info 3	Note 3

<tbody>
</tbody>

Sheet2

	A	B	C	D
1	Brendan
2	Text	Lookup A1 & return: Text 1	Lookup A1 & return: Text 3
3	Info	Lookup A1 & return: Info 1	Lookup A1 & return: Info 3
4	Note	Lookup A1 & return: Note 1	Lookup A1 & return: Note 3

<tbody>
</tbody>

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

 

[Canapone]

Hi in A1= "Brendan" first row data from column Z =IFERROR(INDEX(Responses!$Z$3:$Z$100,SMALL(IF(Responses!$V$3:$V$100=$A$1,ROW($A$3:$A$100)-MIN(ROW($A$3:$A$100))+1),COLUMNS($A$1:A$1))),"")

<tbody>
</tbody>

Second row data from column AA (same array formulas)

=IFERROR(INDEX(Responses!$AA$3:$AA$100,SMALL(IF(Responses!$V$3:$V$100=$A$1,ROW($A$3:$A$100)-MIN(ROW($A$3:$A$100))+1),COLUMNS($A$1:A$1))),"")


Third row data from column AB

=IFERROR(INDEX(Responses!$AB$3:$AB$100,SMALL(IF(Responses!$V$2:$V$100=$A$1,ROW($A$3:$A$100)-MIN(ROW($A$3:$A$100))+1),COLUMNS($A$1:A$1))),"")


All formula must be confirmed with control+shift+enter before to be copied to the right to get the list of concurrencies.

Hope it helps

 

[Matty]

Hi,

Try the following formula in B2 of Sheet2, committed with CTRL+SHIFT+ENTER before copying down and across:

=IFERROR(INDEX(INDEX(Sheet1!$W$2:$Y$4,,MATCH($A2,Sheet1!$W$1:$Y$1,0)),SMALL(IF(Sheet1!$V$2:$V$4=$A$1,ROW(Sheet1!$V$2:$V$4)-ROW(Sheet1!$V$2)+1),COLUMNS($B2:B2))),"")

Matty

 

[Canapone]

Hi all,

another option very similar to Matty's one.

=IFERROR(INDEX(Responses!$Z$3:$AC$101,SMALL(IF(Responses!$V$3:$V$101=$A$1,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),COLUMNS($A$1:A$1)),ROWS($A$1:$A1)),"")

Using Excel 2010 you could avoid array formula

Cheers

 

[sabine]

You are an Excel God Canapone
Thanks so much it worked

 

[Aladin Akyurek]

See:
http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354
which avoids invoking to many array formulas for reasons of efficiency.

 

[Hercules1946]

See:
http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354
which avoids invoking to many array formulas for reasons of efficiency.

Aladin
Another of your masterpieces !

 

[Hercules1946]

Hi all,

=IFERROR(INDEX(Responses!$Z$3:$AC$101,SMALL(IF(Responses!$V$3:$V$101=$A$1,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),COLUMNS($A$1:A$1)),ROWS($A$1:$A1)),"")

Using Excel 2010 you could avoid array formula

Cheers

Hello Canapone
What change in 2010 makes this work without array status?

 

[Canapone]

Hi, thanks to AGGREGATE(15,6,.... where option 15 is SMALL, option 6 is "ignore error value" =IFERROR(INDEX(RESPONSES!$Z$2:$AC$100,AGGREGATE(15,6,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1/(RESPONSES!$V$2:$V$100=$A$1),COLUMNS($A$1:A$1)),ROWS($A$1:$A1)),"")

<tbody>
</tbody>

there is no need to confirm formula with control+shift+enter, but it's not a "green" formula, maybe still too polluting and inefficient.

Hope it helps

 

[Hercules1946]

Thanks. I wasn't aware of that. Array formulae can be a bit daunting and heavy on processing, although its often possible to avoid repeated use of them when blocks of data are being returned, as Aladin describes.