Vlookup Multiple Rows

0
votes

In 'Sheet1', I essentially need to type a person's name in a certain cell, (in this case Jim,), and have it lookup a list of names in Column A on 'Sheet2'.

General Idea

I have success in showing the 1st row (with multiple different styles of forumulas, vlookup, index match.etc) but in most cases, there are 2-4 rows of data. Upon searching for the name, I need it to show row 1,2,3,4 if applicable.

2
excelexcel-formulaformularowsvlookup
use vlookup() or an index() match() combination - advantage of index&match is that the indexing column can be in the middle... This post should give you some ideas : stackoverflow.com/q/35232799/4961700 - Solar Mike
This has been asked and answered many times. In fact, it was answered as little as an hour ago. - user4039065

2 Answers

1
votes

step one: find the first larry

=MATCH(C9,C1:C7,0)

https://i.stack.imgur.com/uL0rm.png

step two, create a range that begins +1 after first match row, and look for next larry

=MATCH(C9,INDIRECT("C" & B11+1):C7,0)

https://i.stack.imgur.com/giYDI.png

step three, fill in my arrays

=INDEX(C1:C7,$B$11)

=INDEX(INDIRECT("E" & $B$11+1):$E$7,$B$12)

https://i.stack.imgur.com/sACGM.png

https://i.stack.imgur.com/Ly8uj.png

0
votes

I finished writing my answer at the same time as @Jeeped's comment and the other answer appeared.

Still worth sharing perhaps because it shows how you can pull across the formula for several columns

=IFERROR(INDEX(Sheet1!$D$2:$I$10,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$10)/(Sheet1!$D$2:$D$10=$D$2),ROW()-ROW($3:$3))-ROW($1:$1),COLUMN()-COLUMN($C:$C)),"")

The data on the first sheet looks like this

enter image description here

And the second sheet is like this

enter image description here