Home

Excel IF help

%3CLINGO-SUB%20id%3D%22lingo-sub-872444%22%20slang%3D%22en-US%22%3EExcel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872444%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20creating%20an%20excel%20sheet%20to%20try%20and%20show%20a%20basic%20configurator.%3C%2FP%3E%3CP%3Ei%20have%20the%20following%20which%20shows%20that%20if%20copier%20desk%20is%20selected%20in%20call%20F9%20then%20the%20cell%20will%20read%20DK-510%20Copier%20desk.%26nbsp%3B%3DIF(F9%3D%22Copier%20desk%22%2C%20%22DK-510%20Copier%20desk%22)%3C%2FP%3E%3CP%3Ei%20would%20like%20the%20cell%20to%20also%20say%20if%20f9%20%3D%20500%20sheet%20tray%20then%20the%20cell%20will%20show%20PF-514.%3C%2FP%3E%3CP%3Ebasically%20i%20want%20to%20be%20able%20to%20chose%20an%20option%20and%20show%26nbsp%3B%20version%20of%20the%20option%20in%20the%20cell.%3C%2FP%3E%3CP%3Ethe%20below%20works%20if%20a%20number%20is%20shown%20(for%20a%20different%20cell)%2C%20but%20i%20need%20the%20same%20for%20the%20selected%20word%2Fwords%20-%20is%20this%20possible%3F%3C%2FP%3E%3CP%3Ei%20thought%20something%20like%20this%20-%26nbsp%3B%3DIF(F9%3D%22Copier%20desk%22%2C%20%22DK-510%20Copier%20desk%22%2C%20IF(F9%3D''500%20Sheet%20tray''%2C''PF-514''))%20-%20but%20this%20doesnt%20work%3C%2FP%3E%3CP%3E%3DIF(F3%26gt%3B657%2C%22INEO%2B658%20(Includes%202x%20500%20sheet%20cassette)%22%2CIF(F3%26gt%3B%3D558%2C%22INEO%2B558%20(Includes%202x%20500%20sheet%20cassette)%22%2CIF(F3%26gt%3B%3D458%2C%22INEO%2B458%20Includes%202x%20500%20sheet%20cassette)%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-872444%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIF%20command%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873308%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414342%22%20target%3D%22_blank%22%3E%40jh1979%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20replace%20doubled%20single%20apostrophes%20on%20double%20apostrophes%20the%20formula%20works%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(F9%3D%22Copier%20desk%22%2C%20%22DK-510%20Copier%20desk%22%2C%20IF(F9%3D%22500%20Sheet%20tray%22%2C%22PF-514%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20I'm%20not%20sure%20when%20that%20was%20introduced%2C%20typing%20the%20post%20or%20in%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873823%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873823%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20this%20now%20works%20-%20i%20think%20i'd%20actually%20made%20an%20error%20in%20my%20typing.%20But%20thanks%20for%20your%20help%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873826%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873826%22%20slang%3D%22en-US%22%3E%3CP%3Ecan%20i%20add%20more%20options%20to%20this%20formula%3F%20i%20would%20like%204%20or%205%20options%20in%20there.%20currently%20there%20are%202%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873839%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873839%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20for%20example%20-%26nbsp%3B%3DIF(F9%3D%22Copier%20desk%22%2C%20%22DK-510%20Copier%20desk%22%2C%20IF(F9%3D%22500%20Sheet%20paper%20feed%20cabinet%22%2C%22PF-514%22%2C%20if(f9%3D%20''TWO%20500%20Sheet%20paper%20feed%20cabinet''%2C''PC-512'')))%20-%20but%20this%20doesnt%20work%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873856%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873856%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20ignore%20previous%20messages%2C%20i%20have%20figured%20it%20out.%20Many%20thanks%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875741%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875741%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414342%22%20target%3D%22_blank%22%3E%40jh1979%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOkay%2C%20so%20far%20so%20good.%20As%20a%20comment%2C%20better%20not%20to%20hardcode%20your%20texts%20into%20the%20formulas%2C%20but%20put%20the%20in%20some%20helper%20range%2C%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20333px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133543iB5B0BB4EA7ABA953%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWith%20that%20your%20formula%20could%20be%20transformed%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(F9%2C%24B%241%3A%24B%2410%2C%24C%241%3A%24C%2410)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879175%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bsorry%20from%20the%20last%20message%20-%20i%20would%20like%20to%20add%20items%20from%20sheet%202%20to%20the%20top%20of%20sheet%204%20if%20nothing%20selected%20from%20sheet%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879683%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414342%22%20target%3D%22_blank%22%3E%40jh1979%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20but%20I%20didn't%20catch.%20Could%20you%20please%20provide%20small%20sample%20to%20illustrate%20the%20task.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jh1979
Occasional Contributor

i am creating an excel sheet to try and show a basic configurator.

i have the following which shows that if copier desk is selected in call F9 then the cell will read DK-510 Copier desk. =IF(F9="Copier desk", "DK-510 Copier desk")

i would like the cell to also say if f9 = 500 sheet tray then the cell will show PF-514.

basically i want to be able to chose an option and show  version of the option in the cell.

the below works if a number is shown (for a different cell), but i need the same for the selected word/words - is this possible?

i thought something like this - =IF(F9="Copier desk", "DK-510 Copier desk", IF(F9=''500 Sheet tray'',''PF-514'')) - but this doesnt work

=IF(F3>657,"INEO+658 (Includes 2x 500 sheet cassette)",IF(F3>=558,"INEO+558 (Includes 2x 500 sheet cassette)",IF(F3>=458,"INEO+458 Includes 2x 500 sheet cassette)")))

8 Replies

@jh1979 

If replace doubled single apostrophes on double apostrophes the formula works

 

=IF(F9="Copier desk", "DK-510 Copier desk", IF(F9="500 Sheet tray","PF-514"))

 

But I'm not sure when that was introduced, typing the post or in Excel

thanks this now works - i think i'd actually made an error in my typing. But thanks for your help - @Sergei Baklan 

can i add more options to this formula? i would like 4 or 5 options in there. currently there are 2@Sergei Baklan 

This for example - =IF(F9="Copier desk", "DK-510 Copier desk", IF(F9="500 Sheet paper feed cabinet","PF-514", if(f9= ''TWO 500 Sheet paper feed cabinet'',''PC-512''))) - but this doesnt work@Sergei Baklan 

Please ignore previous messages, i have figured it out. Many thanks

@Sergei Baklan 

@jh1979 

Okay, so far so good. As a comment, better not to hardcode your texts into the formulas, but put the in some helper range, like

image.png

With that your formula could be transformed to

=LOOKUP(F9,$B$1:$B$10,$C$1:$C$10)
Highlighted

@Sergei Baklan sorry from the last message - i would like to add items from sheet 2 to the top of sheet 4 if nothing selected from sheet 1.

 

@jh1979 

Sorry, but I didn't catch. Could you please provide small sample to illustrate the task.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies