+ Reply to Thread
Results 1 to 10 of 10

Nested IF Function based on text condition

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Angry Nested IF Function based on text condition

    All morning I have been in Excel trying to create a function that will give me the full Currency name based on its acronym.

    For example, Column "X" contains various different currency codes (INR, CAD, USD, HKD, ect.)
    I am trying to draft a function where based on the prefix of that cell, the full currency name should populate.

    For example, if the cell contains CAD=Canadian Dollar.
    But since there are various currency codes, I need to put various conditions.

    So I tried, =IF(X1="ARS","Argentine Peso", OR(X1="CAD",X1="USD",X1="INR",X1="COP",X1="GHS")) - This didn't work, shows false for everything not ARS.

    Then I tried to nest the if functions..
    =IF(X1="ARS", "Argentine Peso", IF(X1="CAD", "Canadian Dollar", IF(X1="ARS", "Argentine Peso"))) - It keeps saying too many arguments. Nothing I try works!

    As humbly as I can ask... pls help

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,185

    Re: Nested IF Function based on text condition

    Hi curious formula seeker and welcome to the forum.

    This is a job for VLookup(,,,False) to do exact matches. In the first column you need to have the TLA, like ARS, CAD,.. etc
    In the second column needs to be the full name like Argentine Peso, Canadian Dollar,.. etc.

    Then look up the TLA using a VLookup and it will return the full words.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,486

    Re: Nested IF Function based on text condition

    Build up a small table somewhere with the first column made up of the acronyms and the second column made up of the appropriate currency name, and then you could use the VLOOKUP function, something like this:

    =IFERROR(VLOOKUP(X1,table,2,0),"")

    where table contains the cell references for where your table occurs.

    Hope this helps.

    Pete

  4. #4
    Banned User!
    Join Date
    01-26-2019
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: Nested IF Function based on text condition

    Hi maybe try this:

    =IFERROR(INDEX($C$2:$C$254,MATCH($X$2,$D$2:$D$254,0)),"")

  5. #5
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Re: Nested IF Function based on text condition

    You guys have been such a big help. Both VLOOKUP and the formula billgyrotech1 posted worked. Just curious if there really is no actual way to nest the function in a conditional statement?

  6. #6
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,232

    Re: Nested IF Function based on text condition

    if you are asking is there a way to work it with a nested if function there is. Your second attempt in post #1 is close.
    =IF(X1="ARS", "Argentine Peso", IF(X1="CAD", "Canadian Dollar", IF(X1="ARS", "Argentine Peso","")))
    for one thing, your third argument is a repeat of the first AND you didn't add the "else if" part which I added after the third argument. So that would be a way to work a nested if statement though if you have many of these it is more efficient to use the vlookups or index/matches proposed by others.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam C

  7. #7
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Re: Nested IF Function based on text condition

    SamboKid..

    You are a genius, the formula worked.

    TYSM!!!

    This is the final Formula guys..



    =IF(X2="ARS", "Argentine Peso", IF(X2="CAD", "Canadian Dollar", IF(X2="USD", "US Dollar", IF(X2="INR", "Indian Rupee", IF(X2="COP", "Columbian Peso", IF(X2="GHS", "Ghanian Cedi",""))))))

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,185

    Re: Nested IF Function based on text condition

    Hello again,

    There is a newer Excel function called Switch that will do what you want also. See it at:

    https://www.techonthenet.com/excel/f.../switch_ws.php

  9. #9
    Registered User
    Join Date
    02-12-2019
    Location
    new york
    MS-Off Ver
    office 365 proplus 1811
    Posts
    13

    Re: Nested IF Function based on text condition

    MarvinP … You are awesome. Just tried it, worked with no problems.

    Thank you all for your assistance and solutions offered!!

  10. #10
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,232

    Re: Nested IF Function based on text condition

    you're welcome, AND thank you for the rep!
    BTW MarvinP, interesting info on switch, unfortunately it won't work for my version of excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1