( 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
a b c d
name item thing (%) <-- whats outmanuvering me at the moment
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.