Forum Discussion
MULTIPLE IF CONDTITIONS OVER 64
Greetings! I have a formula with more than 64 nested items and I need to simplify it so that I can reference a labor matrix. =IF(B2=0.1,Gas!C35,IF(B2=0.2,Gas!D35,IF(B2=0.3,Gas!E35,IF(B2=0.4,G...
I didn't look at every single item but it sure looks like an INDEX should work here something like:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)
EDIT: I added 1 to the row and cols and changed the ref range to accommodate the 0 cases
How do I make it populate into C2 the correct value from the grid. Example, in B2 I want to enter 2.3 and it pull the value from E37 or 2.3. B2 will be a cell that is manually entered labor hours, 1, 2.2, 3.3, etc.
Thank you for your assistance!
Thank you for your assistance!
- From what I see, you want a value in B2 to define the location in the grid such that the integer portion indicates the row and the decimal portion is the column. so the formula:
=INDEX(Gas!A34:K50, INT(B2)+1, MOD(B2,1)+1)
says to start at cell A34 and then move down the integer portion of B2 +1 rows and to the right the decimal portion of B2 +1 columns.
so in you example the value 2.3 would move down 3 rows (2+1) and to the right 4 columns (3+1) so starting at A34 would go down to row 37 and to the right to Col E and return the value in cell E37.Entering this into the spreadsheet it does not search the column as described. Your help is appreciated.
The formula wasn't intended to return data from a column, rather the 2D range
returnArray = Gas!B35:K51If the values you are searching for always form an arithmetic series, the mtarler's approach of turning the value into an index would be best. If the search values could be irregular, then a search would be required.
With 365 beta, the lookup (XLOOKUP or INDEX) could be based upon the single column-array
= TOCOL(returnArray)
