Jan 07 2022 11:37 AM
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))))
Jan 07 2022 12:03 PM
See attached file.
Jan 07 2022 12:21 PM
@OliverScheurich 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.
Jan 07 2022 02:43 PM - edited Jan 07 2022 02:48 PM
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.