Vlookup with multiple occurrences

[brooksc29]

I am trying to write a Vlookup formula that will find a name (entered in cell FILTER!A1) in col B of Video!A2:U2000 and pull the data across that row back to the FILTER worksheet.

The only problem is there are multiple occurrences of the name in A1 in column B on the Video worksheet.

How can I write the formula to say - for every appearance of the name in A1, pull back the data across that row for the 1st appearance, then on the next row on FILTER worksheet pull the next occurrence and all the data across that row.

 

[brooksc29]

i'm messing around with a INDEX formula and it's close to pulling back the right name but it's off by one cell (pulling back A7 when it should be A6)

this is the formula I'm using
=INDEX(VIDEO,SMALL(IF('2011 Amateur Video'!$B$2:$B$2000=$A$1,COLUMN('2011 Amateur Video'!$A$2:$A$2000)),COLUMN(1:1)),0)

and I'm not sure if I'm going in the right direction with this at all...

please help!

 

[ptworthing]

You have probably have this fixed, I have resolve similar problems by using countif to work out the number of rows that contain a specific value.
If they are consecutive items in the list then its simple - use match() to identify the row they start. Then use Index() to pull the entries into seperate rows.

If non-consecutive, then try to use a new column as a running counter using an IF command, If the row is what you want then add one, if not then don't - Then use match to find the first increment of the running counter

 

[Aladin Akyurek]

I am trying to write a Vlookup formula that will find a name (entered in cell FILTER!A1) in col B of Video!A2:U2000 and pull the data across that row back to the FILTER worksheet.

The only problem is there are multiple occurrences of the name in A1 in column B on the Video worksheet.

How can I write the formula to say - for every appearance of the name in A1, pull back the data across that row for the 1st appearance, then on the next row on FILTER worksheet pull the next occurrence and all the data across that row.

On FILTER

A1 houses a look up value of interest

A2, just enter:

=COUNTIF(Video!B2:B2000,A1)

In A3 enter Idx as header.

A4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)<=$A2,SMALL(IF(Video!$B$2:$B$2000=$A$1,
    ROW(Video!$B$2:$B$2000)-ROW(Video!$B$2)+1),ROWS($A$4:A4)),"")

B4, just enter, copy across as far as needed, and copy down:

=IF(N($A4),INDEX(Video!A$2:A$2000,$A4),"")