Tuesday, August 13, 2013

Google Spreadsheet Arrayformula Vlookup


( by WhoSoLovesUs )

Hi, I'm using a script that queries GA and populates a sheet (Sheet A) with a list of keywords. The list changes from week to week, some keywords will come and go and the order will change from week to week

Sheet B has a static list of desired keywords to track, with a couple of associated metrics beside them

Since sheet A will be in flux, I need a function that will search the cells on Sheet A to see if they match the entries on the "static" list of Sheet B and if so, populate columns B and C on Sheet B with the values of D and E on Sheet A

If the keyword isn't present in sheet A, I'd like to display (no data) or something like that in the cell

Thanks :)


Have a look at the following screenshot of "Sheet A":

Have a look at the following screenshot of "SheetB":

I have the following formula in Cell B2:
=arrayformula(if(A2:A="";"";iferror(vlookup(A2:A;'Sheet A'!A12:G;{4,5}*sign(row(A2:A));false);"No Data")))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


No comments:

Post a Comment