SOLVED

# IFS formula

Brass Contributor

# IFS formula

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

6 Replies
best response confirmed by excel_learner (Brass Contributor)
Solution

# Re: IFS formula

=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.

# Re: IFS formula

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

# Re: IFS formula

=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.

# Re: IFS formula

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

# Re: IFS formula

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.

# Re: IFS formula

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