Odtnhj

I'm trying to created a nested IF statement that says, for example, IF A2 is equal to x, x, x posting codes then pull data from the revenue column, IF A2 is equal to y, y, y posting codes then pull data from Bad Debt column and so on for 6 different possible results. Only 3 of the 6 are VLOOKUPs. The other 3 are just if A2=Z then "1024".

I will copy what code I currently have, but it's not pretty. Any help is appreciated. Thank you.

=IF(G2="100",G2="101",G2="102",G2="105",G2="106",G2="170",G2="171",G2="173",G2="200",G2="210",G2="220",G2="230",G2="250",G2="300",G2="301",G2="302",G2="304",G2="305",G2="306",G2="307",G2="308",G2="309",G2="310",G2="312",G2="313",G2="314",G2="315",G2="316",G2="317",G2="318",G2="319",G2="320",G2="321",G2="322",G2="323",G2="324",G2="325",G2="326",G2="327",G2="328",G2="329",G2="330",G2="331",G2="332",G2="333",G2="334",G2="335",G2="336",G2="337",G2="338",G2="339",G2="340",G2="341",G2="342",G2="343",G2="344",G2="345",G2="346",G2="360",G2="370",G2="372",G2="379",G2="382",G2="383",G2="385",G2="390",G2="402",G2="403",G2="404",G2="405",G2="414",G2="415",G2="416",G2="417",G2="418",G2="419",G2="420",G2="421",G2="425",G2="427",G2="428",G2="429",G2="440",G2="441",G2="442",G2="443",G2="444",G2="445",G2="447",G2="472",G2="477",G2="480",G2="501",G2="600",G2="700",G2="800",G2="900",vlookup('AR Credit'!A2,Guarantors!A:D,3,FALSE),IF(G2="347",G2="384",G2="399",G2="406",G2="499",G2="801",vlookup('AR Credit'!A2,Guarantors!A:D,5,FALSE),IF(G2="348",G2="354",G2="355",G2="357",G2="377",G2="601",vlookup('AR Credit'!A2,Guarantors!A:D,4,FALSE),IF(G2="120",G2="150",G2="151",G2="152",G2="153",G2="154","1051",IF(G2="400","1385",IF(G2="500","2036",IF(G2="172","3783","Null"))))))))

One way of fixing your issue is by using OR, specifically wrapping your cases in it like in this example:

=OR(G2="100",G2="101",G2="102",G2="105")

This will result in TRUE if any of conditions is met, otherwise FALSE.

There are numerous errors with your formula. You could get it working by following Zipa's answer and using the OR function. However, it would be a nightmare to maintain if anything changes in future.

I would recommend creating a look up table for the values and results. It could then be queried like so:

=IF(COUNTIFS($E$2:$E$98,$A2)>0,$E$1,
 IF(COUNTIFS($F$2:$F$98,$A2)>0,$F$1,
 IF(COUNTIFS($G$2:$G$98,$A2)>0,$G$1,
 IF(COUNTIFS($H$2:$H$98,$A2)>0,$H$1,
 IF(COUNTIFS($I$2:$I$98,$A2)>0,$I$1,
 IF(COUNTIFS($J$2:$J$98,$A2)>0,$J$1,
 IF(COUNTIFS($K$2:$K$98,$A2)>0,$K$1,"NULL")
 )
 )
 )
 )
 )
)

See the image below for illustration:

Where I have put "Opt1", "Opt2" in the results row, you can put your VLOOKUPs (although I would recommend INDEX/MATCH instead) or your hard coded values.

This way, you can easily see (and modify) which group each code is in. Even adding more groups wouldn't be too difficult.

Obviously, you could move the table onto a different sheet if you wished.