Apply VlLOOKUP till last non-empty row in google sheets

1
votes

I have an automatically expanding google in which I am applying a Vlookup formula to extract values from another sheet matching the values from the main sheet.

This is the Vlookup formula-

=VLOOKUP(A2, IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"),4,0)

I want the formula to be auto applied to the new entries which get auto added in the main sheet. I used this formula I found on a website to apply this formula to the whole column also taking care of any empty cells in between.

=ArrayFormula(IF(ISBLANK(A2:A), "", VLOOKUP(A2:A, IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"),4,0)))

My problem is that after using this formula when a new entry is auto added to column A in the sheet, it gets added to 1001th row instead of the next non-empty row, because this formula is being applied to whole column.

Is there some way to apply the Vlookup to just be applied till last non-empty row, so that my next automatic entry gets added in the next empty row? Or any other alternative solution? Thanks for the help!

2
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupimportrange

2 Answers

1
votes

try:

=ARRAYFORMULA(QUERY(IF(ISBLANK(A2:A),,VLOOKUP(A2:A, 
 IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"), 4, 0)), 
 "where Col1 is not null", 0))

0

0
votes

Since we don't have sheet for reference assuming new data is added to new column.

Try this fromula =ARRAYFORMULA(If(A2:A="","",VLOOKUP(A2:A, IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "Sheet1!B3:E420"),4,0)))

This will do vlookup once there is new data in Column 'A'