VLOOKUP Skip Row If Not A Match?

[Pmarsden94]

Hi,

I know this is Excel based but most of the formulas work for Google Sheets.

I am trying to get VLOOKUP to SKIP a row if the data is not a MATCH....

See image below of the Data Source

Then I have 10 separate tabs where we input the Agent Allocation which a VLOOKUP on the separate tabs will pull this data through. See below the image where the tab is trying to pull the data too.

Instead of returning the exact row location where the matching data is I want it to skip the rows that don't match and just keep adding the next matching row. Like the picture below....

I would like to resolve this without Script as this requires every user to have a Gmail login in order for the Script to work for each person.

Thanks!!!

 

[Fluff]

Whilst I know nothing about Sheets, rather than using a lookup formula why not use the Filter function?

 

[jasonb75]

Insert a new column 'A' in the output sheet so that everything is moved to the right by 1 column.

Enter a heading in A1 (this will not work if A1 is empty) then enter this formula into A2 and fill down as necessary.

=IF(A1="","",IFERROR(AGGREGATE(15,6,ROW(Source!$A$2:$A$200)/(Source!$A$2:$A$200="Name"),ROWS($A$2:$A2)),""))

In B2, then fill right and down

=IF($A2="","",INDEX(Source!B:B,$A2))

Note that in both formulas, Source! refers to the first image in your post. "Name" refers to the name of the agent that should be allocated on that sheet.

 

[Fluff]

@jasonb75
I don't think Sheets has an aggregate function.

 

[jasonb75]

Oops, that could be a problem.

Think I prefer your suggestion though, I had forgotten that sheets does have a filter function.

 

[Pmarsden94]

Insert a new column 'A' in the output sheet so that everything is moved to the right by 1 column.

Enter a heading in A1 (this will not work if A1 is empty) then enter this formula into A2 and fill down as necessary.

=IF(A1="","",IFERROR(AGGREGATE(15,6,ROW(Source!$A$2:$A$200)/(Source!$A$2:$A$200="Name"),ROWS($A$2:$A2)),""))

In B2, then fill right and down

=IF($A2="","",INDEX(Source!B:B,$A2))

Note that in both formulas, Source! refers to the first image in your post. "Name" refers to the name of the agent that should be allocated on that sheet.

Hi, Thanks for this...

I have done this but is returning nothing...

 

[Pmarsden94]

Oops, that could be a problem.

Think I prefer your suggestion though, I had forgotten that sheets does have a filter function.

The filter option does work to remove blanks but the agent would have to keep refreshing this... I am trying to make it as automated as possible.

 

[Fluff]

I am not referring to using the autofilter, but the filter function.
Something like

=FILTER(Data!A2:M1000,Data!A2:A1000="Dan Carter")

 

[Pmarsden94]

I am not referring to using the autofilter, but the filter function.
Something like

=FILTER(Data!A2:M1000,Data!A2:A1000="Dan Carter")

Where on the receiving sheet would I put this formula?

 

[Fluff]

Wherever you want the data to start.