Thursday, August 23, 2012

Looking up data based on other values without nesting ifs

Looking up data based on other values without nesting ifs


I am using the values in columns G-J to lookup data in columns B-E and place it columns L-O,  I am doing this by "nesting ifs" in formulas in columns L-O.   (the data in B-E and G-J) is actually brought in from external sources, but for simplicity sake, is static in the example spreadsheet).

I am looking to expand this to about 20 columns of data and values (in the future, there may be more columns of data than volumes).

Is there a way to do this without nesting ifs.....I am sure I will hit a limit on google's nesting, and it's a lot of work to change all the time.

Here is the screenshot of the spreadsheet:



Solution:

Here is the formula for all cells in range L3:O15
=OFFSET(B1;ROW()-1;OFFSET(A1;ROW()-1;COLUMN()-11)-1)

I hope this will help you to get rid of nested ifs formulas. 

And please comment your views here on my blog.


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.

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,
Kishan,

No comments:

Post a Comment