Saturday, June 29, 2013

Google Spreadsheet Advanced Query

Question:

(by Daniel)


Good day

I'm working in this spreadsheet 

Screenshot of Sheet 'data':


Screenshot of Sheet 'Lookup':


In C2 want to put a function were It should look up first for A2 in =Lookup!A1:K1

Like using HLOOKUP depending what is it it will use then VLOOKUP lets say of B2 and prompt me the value that matches in the sheet LOOKUP next to the value that it should be

Pretty much Like using
First HLOOKUP to find which column to use then VLOOKUP to check which of the rows to use and then to display the value next to it

In this case, Social network: Instagram and Type: followers, so it would display me "j"
quite mixed up with this to make it simple, rather than do IF's and HLOOKUP's
thank you.


Solution:

Put the following formula in Cell C2 of Sheet 'data':
=query(arrayformula('Lookup'!A:Z);"select Col" & MATCH(A2;'Lookup'!A1:1;0)+1 & " where Col" & MATCH(A2;'Lookup'!A1:1;0) & "='"& B2 &"'")

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 

Thanks,


No comments:

Post a Comment