Saturday, September 14, 2013

Google Spreadsheet Mutli Lookup formula


( by George Randell )

Using Windows 7 on Firefox

I'm probably being dim but I can't for the life of me workout/remember how to set up a function that using a lookup table can work out via column and row from 2 other cells

main sheet

a          b         c          d         
name   item    thing     (%) <-- whats outmanuvering me at the moment

Lookup table
         thinga thingb thingc
itema  5%      10%    13%
itemb  6%      11%     15%
itemc  7%      13%     20%

column b and c a already data validated as lookups for (b = item colum and c = thing row) I'm after the formla to 'add' column b and c so d automatically looks up the percentage relating to both.
I have a funny feeling this is alot simpler than i'm making it..any help would be much apreciated


Have a look at the following screenshot of Sheet "Lookup Table":

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

In the above sheet I have the following formula in Cell D1:
=query(transpose(query('Lookup Table'!A1:D4;"select * where A = 'item"&B1&"'";1));"select Col2 where Col1 = 'thing"&C1&"'";0)

In the above formula change "Lookup Table" to the name of the sheet in which you have the lookup table 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: 


No comments:

Post a Comment