[Solved] Copying and changing IF statements

[brookc84]

I have an "IF" statement shown below, and would like to populate it through a few hundred rows in my spreadsheet. The only value I want changed is A19. I need it to be increased by the value of 1 for each row. However, when I try the standard way of copying the formula down the spreadsheet... ALL the values in the formula are increased by 1:

"=IF(A19=I6;K6;IF(A19=I7;K7;IF(A19=I8;K8;IF(A19=I9;K9;IF(A19=I10;K10;IF(A19=I11;K11;IF(A19=I12;K12;IF(A19=I13;K13;IF(A19=I14;K14;IF(A19=I15;K15;IF(A19=I16;K16;IF(A19=I17;K17;IF(A19=I18;K18;IF(A19=I19;K19;IF(A19=I20;K20;IF(A19=I21;K21;IF(A19=I22;K22;IF(A19=I23;K23;IF(A19=I24;K24;IF(A19=I25;K25;IF(A19=I26;K26;IF(A19=I27;K27;IF(A19=I28;K28;IF(A19=I29;K29;""))))))))))))))))))))))))"

[jeffs12]

To copy the way you describe you need to learn the difference between relative and absolute addressing. However, it appears that what you are doing is trying to compare the value in A19 with the values in I6 thru I29 and return the corresponding value from K6 thru K29. If this is in fact the case, you would be better off using the LOOKUP function. Provided that the values in I6 thru I29 are sorted in ascending order your formula would look like =LOOKUP(A19;$I$6:$I$29;$K$6:$K$29). You can then drag this formula to your many rows.

Further consideration will be needed to handle cases where the value of A19 does not appear in your lookup range. As mentioned in reply to your other post, a sample of your file will help generate a more complete answer, especially from the more expert people on this forum.

[keme]

You can lock the address components you don't want to change by using a prefix dollar sign.

Code: Select all

=IF(A19=$I$6;$K$6;IF(A19=$I$7;$K$7;IF(A19=$I$8;$K$8;IF(A19=$I$9;$K$9;IF(A19=$I$10;$K$10;IF($A$19=$I$11;$K$11;IF(A19=$I$12;$K$12;IF(A19=$I$13;$K$13;IF(A19=$I$14;$K$14;IF(A19=$I$15;$K$15;IF(A19=$I$16;$K$16;IF(A19=$I$17;$K$17;IF(A19=$I$18;$K$18;IF(A19=$I$19;$K$19;IF(A19=$I$20;$K$20;IF(A19=$I$21;$K$21;IF(A19=$I$22;$K$22;IF(A19=$I$23;$K$23;IF(A19=$I$24;$K$24;IF(A19=$I$25;$K$25;IF(A19=$I$26;$K$26;IF(A19=$I$27;$K$27;IF(A19=$I$28;$K$28;IF(A19=$I$29;$K$29;""))))))))))))))))))))))))

LOOKUP() works fine on sorted data, as jeffs12 mentioned above, and you can easily extend the lookup table 10-fold by changing a number in 2 places. If there is no exact match, LOOKUP() will return the highest position not larger than the search value. To ensure only exact matches are returned, you would have to check for equality:

Code: Select all

=IF(LOOKUP(A19;$I$6:$I$29)=A19;LOOKUP(A19;$I$6:$I$29;$K$6:$K$29);"")

An even simpler solution can be had using VLOOKUP(), which can also work on unsorted data (using the optional 4th parameter set to zero)

Code: Select all

=VLOOKUP(A19;$I$6:$K$29;3;0)

The above will show the error message #N/A if A19 does not match an entry in the table. To show nothing, test whether the lookup returns the #N/A error:

Code: Select all

=IF(ISNA(VLOOKUP(A19;$I$6:$K$29;3;0));"";VLOOKUP(A19;$I$6:$K$29;3;0))