# VLOOKUP vs INDEX?

#### sabine

##### New Member
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

Last edited:

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Canapone

##### Active Member
 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

Last edited:

#### Matty

##### Well-known Member
Hi,

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

Code:
``=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

##### Active Member
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

##### New Member
You are an Excel God Canapone
Thanks so much it worked

#### Hercules1946

##### Well-known Member
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

##### Active Member
 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

Last edited:

#### Hercules1946

##### Well-known Member
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.

Replies
6
Views
313
Replies
1
Views
78
Replies
4
Views
249
Replies
3
Views
169
Replies
3
Views
557

1,190,834
Messages
5,983,159
Members
439,824
Latest member
jr599

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back