IF Statement not working


I'm trying to put a formula together that's based on select criteria in the same row. This is linked to another table called Reference table. In the Master I want to get values in category column AD from the category column in Reference Table.

The Reference Table essentially matched all the criteria in master sheet and provides correct Category according to what's in the rows Y, X, Z, AA, AB.

The criteria's are Y, X, Z, AA, AB    they are all in Master sheet. I have tried using this formula but I'm not getting results for all categories,

I have attached the file. Any help on how i can construct this formula

This is the formula i am using but not getting all results.


=IF([@TIER]='Reference Table'!$E$42,'Reference Table'!$G$42,IF(AND([@TIER]='Reference Table'!$E$40,[@Policy]='Reference Table'!$F$40),'Reference Table'!$G$40,IF(AND([@TIER]='Reference Table'!$E$41,[@Policy]='Reference Table'!$F$41),'Reference Table'!$G$41,VLOOKUP([@Banding]&[@Pressure]&[@TIER]&[@Material],CHOOSE({1,2},'Reference Table'!B$2:B$42&'Reference Table'!$C$2:$C$42&'Reference Table'!$E$2:$E$42&'Reference Table'!$A$2:$A$42,'Reference Table'!$G$2:$G$42),2,FALSE))))

3 Replies


See attached file.

@Quadruple_Pawn I am trying the same formula on my sheet but its not working for some reason.

Any reason why that could be?

Also why do you have this { before equals and at end of formula.



When you filter the "Master" sheet as shown in the attached file you can see which banding entries cause the NV errors. For example the entries in cell Y2 of the "Master" sheet ( 4-5” ) and in cell B33 ( 4-5ˮ ) of the "Reference Table" sheet are different. This is not obvious but ” and ˮ are different and therefore the formula returns the NV error which is absolutely correct.


The solution is to copy the value of cell Y2 of the "Master" sheet and then paste it into cell B33 of the "Reference Table" sheet. Immediately the formula returns CI/SI/DI LP 4"-5" in cell AD2 of the "Master" sheet.


Then copy <=3” and 4-5” from the "Master" sheet and paste them accordingly into the cells (B2, B3, B6, B7, B11, B12  and so on) of the "Reference Table" sheet.