Alternative to multiple IF limits

[Matt Simms]

Dear All,

I have the following problem:

I have a spreadsheet with two worksheets - first one is the master input
sheet, the second is performing all the calculations behind the scenes. On
the second one I have a table setup which works for cross reference - it has
30 columns C2:AG containing headers incrementing by 100 which are width
values ie. 1000,1100,1200,1300,1400..4000. The table has 30 rows B3:B32 with
row headers again incrementing by 100 which are height values - 1000,1100
etc. Each colum and row value has a crossreferenced value within the table
ie. 1300 (F2) and 1900(B12) meet at F12 with a value of 600.

Currently we have been entering a value into the master sheet for example
1252 for width and height 1453 - I have then been setting a value of a two
cells using:
Cell 1:
=IF(cs!B2+cs!M2<1001,"C",IF(cs!B2+cs!M2<1101,"D",IF(cs!B2+cs!M2<1201,"E",IF(cs!B2+cs!M2<1301,"F",IF(cs!B2+cs!M2<1401,"G",IF(cs!B2+cs!M2<1501,"H",IF(cs!B2+cs!M2<1601,"I",IF(cs!B2+cs!M2<1701,"J","!"))))))))
Cell 2:
=IF(cs!C2+cs!N2<1001,"3",IF(cs!C2+cs!N2<1101,"4",IF(cs!C2+cs!N2<1201,"5",IF(cs!C2+cs!N2<1301,"6",IF(cs!C2+cs!N2<1401,"7",IF(cs!C2+cs!N2<1501,"8",IF(cs!C2+cs!N2<1601,"9",IF(cs!C2+cs!N2<1701,"10","!"))))))))

This would then produce in cell 1:F cell 2:8 which I have then been using
=CONCATENATE(B52,C52) to populate F8 into that cell and then using
=INDIRECT(D52) for the value of the cell that the concatenate relates to.
Although probably a long winded way of working out the value it works - I'm
open to alternative suggestions - the main problem I have is that I obviously
need 30 IF statements to get this to work like this - which I know Excel
won't let me - so anyone point me in the direction of a solution that works.

Thanks in advance

Matt

 

[Duke Carey]

You can easily use the MATCH() function to figure out which column and which
row to use. Those two values would be arguments to the INDEX() function

 

[Matt Simms]

Hi Duke,

Thanks for replying - I tried using the match() function before but if I was
entering 1252 for the value - I found it found the location for 1200 rather
than 1300 as we have to round up the value, and by adding 1 to the location
it caused problems if it was the exact number being searched ie. 1300 would
then return the position for 1400 - I may be doing something wrong.

 

[Duke Carey]

You want to use

=MATCH( value, range, -1)


From the Help file on Match(), regarding the 3rd argument:

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft
Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal
to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to
lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

 

[Matt Simms]

Doesn't work as the values of the cell ascend - I've even tried changing the
formula to descend ie. C32:C3 - but it automatically swops back round

 

[Duke Carey]

You'd have to re-order the columns from high to low; same iwth the rows.

If you don't want to do that, use

=MATCH(ROUNDUP(value,-2),array,1)

=ROUNDUP(1225,-2) returns 1300
=ROUNDUP(1200,-2) returns 1200