IFS formula

[excel_learner]

Hi All,

I'm Struggling to create an IFS formula for my workbook.

I have been trying to get the correct category for each cell based on values in cells in adjacent row, these are columns BI , BJ, BK, BL, BM. I have a separate sheet under Tab "Forecast Lookup" which has the categories I need to used in main forecast input. I want to fill in column BN in Forecast Input tab but a I keep getting #N/A.  

In short I'm trying to get a value from the categories in forecast lookup tab, based on Diameter,Pressure,Policy,Tiers,Material in Forecast input and looking these up from forecast lookup tab.

Any help appreciated

[#2 · Accepted solution]

@excel_learner 

=IF('Forecast Input'!BI2='Forecast Lookup'!$E$44,'Forecast Lookup'!$G$44,IF(AND('Forecast Input'!BI2='Forecast Lookup'!$E$42,'Forecast Input'!BH2='Forecast Lookup'!$F$42),'Forecast Lookup'!$G$42,IF(AND('Forecast Input'!BI2='Forecast Lookup'!$E$43,'Forecast Input'!BH2='Forecast Lookup'!$F$43),'Forecast Lookup'!$G$43,VLOOKUP(BF2&BG2&BI2&BJ2,CHOOSE({1,2},'Forecast Lookup'!$B$2:$B$44&'Forecast Lookup'!$C$2:$C$44&'Forecast Lookup'!$E$2:$E$44&'Forecast Lookup'!$A$2:$A$44,'Forecast Lookup'!$G$2:$G$44),2,FALSE))))

This could be what you are looking for. I have to enter formula with ctrl+shift+enter as i don't work with Office365 or 2021. 

In Forecast lookup table i adjusted some cells for example 6-7"  to  6"-7"  , otherwise the lookup wouldn't work.

There are 5 entries left with #NA because material PE isn't listed in table Forecast Lookup.

[#3]

Excellent Stuff, Thank you so much.
Do you know how this will look in a IFS Syntax ?

[#4]

@excel_learner 

=IFS('Forecast Input'!BI2='Forecast Lookup'!$E$44,

'Forecast Lookup'!$G$44,

AND('Forecast Input'!BI2='Forecast Lookup'!$E$42,'Forecast Input'!BH2='Forecast Lookup'!$F$42),

'Forecast Lookup'!$G$42,

AND('Forecast Input'!BI2='Forecast Lookup'!$E$43,'Forecast Input'!BH2='Forecast Lookup'!$F$43),

'Forecast Lookup'!$G$43,

'Forecast Input'!BI2="T1",

VLOOKUP(BF2&BG2&BI2&BJ2,CHOOSE({1,2},'Forecast Lookup'!$B$2:$B$44&'Forecast Lookup'!$C$2:$C$44&'Forecast Lookup'!$E$2:$E$44&'Forecast Lookup'!$A$2:$A$44,'Forecast Lookup'!$G$2:$G$44),2,FALSE))

It should be like this. But i can't check if formula actually works because i don't work with Office365 or 2021.

[#5]

I keep getting a #value error so i have stuck to the IF syntax.
For some reason I get #N/A for any T2A = criteria Policy & T2

[#6]

@excel_learner 

In Forecast Lookup table there was originally "Policy "  (with a space after the word Policy) instead of "Policy" in the row with T2 and T2A. I changed this and then the VLOOKUP worked fine. You can check if there is "Policy " or "Policy" entered now. In which row does the #value error appear?

Of course i suggest to work with IF formula as i have tested this formula and i can't check if the IFS formula works.

[#7]

Hi Yes this worked there was a space after Policy which caused the #N/A
Thank you @OliverScheurich