Nested IFs Excel - Alternative

user_1756996 0
Viewed 88

I have a large data set with multiple columns - each row has a case type and a contract value. On another sheet I have a mapping table (shown below) of case types and value ranges that decide what "Type" each row should be assigned too.

For example a row with case type 1 and a contract value of $20,000,000 would be type 1 - a row with Case type 3 with a value of $4,000,000 would be Type 3 as it falls between the value ranges for type 3 (N4 and O4)

I know I could hardcode an IF statement in 14 helper columns to fulfill for each Case Type and then combine cells that aren't blank but I'd love to know is there a smarter way of doing this?

enter image description here

if-statement excel nested
1 Answers

First of all, the header of the mapping table data set needs to be adjusted. Unmerge those cells that are merged and then give each column a title. Doing so should result in something similar to this:

example of header


I then created a results table on a second worksheet that looks like this:

results table


It may be grossly over-simplified, but hopefully you can modify the formula as needed.

Now here's the formula I created in C2:

{=INDEX(MappingTable!$K$1:$S$1,1,SUMPRODUCT(IF(B2<INDIRECT("MappingTable!"&ADDRESS(MATCH($A2,MappingTable!$I$2:$I$15,0)+1,11)&":"&ADDRESS(MATCH($A2,MappingTable!$I$2:$I$15,0)+1,19)),1,0))+1)}

Note: This is an array formula and must be committed with Ctrl+Shift+Enter. The curly braces - { } - that surround the formula above will not be entered by the user, but will be created once the formula is committed as described above.

KEY NOTES:
B2 will reference the Contract Value in your results table.

"MappingTable!" is a hardcoded value that should be the name of your sheet plus the exclamation point at the end.

$A2 will reference the Case Type in your results table.

MappingTable!$I$2:$I$15 is an absolute reference to the row headers on the mapping table.

11, which is found in the first ADDRESS function, is a reference to Column K on the mapping table worksheet. It can also be written as COLUMN(K:K).

19, which is found in the secondADDRESS function, is a reference to Column S on the mapping table worksheet. It can also be written as COLUMN(S:S).

user_64783630
Related