# VLOOKUP Skip Row If Not A Match?



## Pmarsden94 (Nov 19, 2020)

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 (Nov 19, 2020)

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


----------



## jasonb75 (Nov 19, 2020)

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 (Nov 19, 2020)

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


----------



## jasonb75 (Nov 19, 2020)

Oops, that could be a problem.

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


----------



## Pmarsden94 (Nov 19, 2020)

jasonb75 said:


> 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.
> 
> ...


Hi, Thanks for this... 

I have done this but is returning nothing...


----------



## Pmarsden94 (Nov 19, 2020)

jasonb75 said:


> 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 (Nov 19, 2020)

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



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


----------



## Pmarsden94 (Nov 19, 2020)

Fluff said:


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


Where on the receiving sheet would I put this formula?


----------



## Fluff (Nov 19, 2020)

Wherever you want the data to start.


----------



## Pmarsden94 (Nov 19, 2020)

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!!!


----------



## Pmarsden94 (Nov 19, 2020)

Fluff said:


> Wherever you want the data to start.





Fluff said:


> Wherever you want the data to start.


Worked a treat, can over complicate these things sometimes but keeping it simple was the best. Thanks for the help!!


----------



## Fluff (Nov 19, 2020)

Glad we could help & thanks for the feedback.


----------



## varunaron (Dec 3, 2020)

Not all formulas in Excel will work in sheets.


----------

