Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: I'm guessing this is a basic IF formula question...

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default I'm guessing this is a basic IF formula question...

    If I have a list of names in column A and want to search that column for a subset of those names, and for "true" results generate the text "PCG" in column B of the corresponding row, which formula do I need to enter for column B? I'd like to do this as an array formula for all of column A and include approximately 30 names to search for. Visual example, searching column A for the names "John Smith" "Adam Pierce" and "Lisa Saxon" would generate "PCG" in column B....

    Phil Bauer
    John Smith PCG
    Jodi Arlington
    Adam Pierce PCG
    Lisa Saxon PCG
    Joe Fordham

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    Hi,

    Perhaps you can try something like this:

    Excel 2013
    ABCD
    1Original listResult columnLookup list
    2Phil BauerAdam Pierce
    3John SmithPGCJohn Smith
    4Jodi ArlingtonLisa Saxon
    5Adam PiercePGC
    6Lisa SaxonPGC
    7Joe Fordham

    Sheet1



    In B2 and copy down enter the formula:

    Code:
    =IFERROR(
        IF(A2 = LOOKUP(A2, D$2:D$4), "PGC", ""),
        "")
    Note, you must ensure the lookup list in column D is sorted in ascending order for this method to work.

    An alternative method that doesn't rely on the lookup list being sorted, might be something like this in B2 and copy down:

    Code:
    =IF(COUNTIF(D$2:D$4, A2) > 0, "PGC", "")
    However this method will be significantly slower with a large list.

  3. #3
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    Hello bg61380, welcome to MrExcel

    You could use this formula syntax in B2 copied down

    =IF(ISNUMBER(MATCH(A2,{"John Smith","Adam Pierce","Lisa Saxon"},0)),"PCG","")

    or list all of your 30 names somewhere, e.g. in Z1:Z30 and then use that range in the above formula, e.g.

    =IF(ISNUMBER(MATCH(A2,Z$1:Z$30,0)),"PCG","")

    copy formula down column

  4. #4
    New Member
    Join Date
    Jan 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    Quote Originally Posted by circledchicken View Post
    A B C D
    1 Original list Result column Lookup list
    2 Phil Bauer Adam Pierce
    3 John Smith PGC John Smith
    4 Jodi Arlington Lisa Saxon
    5 Adam Pierce PGC
    6 Lisa Saxon PGC
    7 Joe Fordham
    Note, you must ensure the lookup list in column D is sorted in ascending order for this method to work.

    An alternative method that doesn't rely on the lookup list being sorted, might be something like this in B2 and copy down:

    Code:
    =IF(COUNTIF(D$2:D$4, A2) > 0, "PGC", "")
    However this method will be significantly slower with a large list.
    Two questions--when you said "must ensure the lookup list in column D is sorted in ascending order" do you mean alphabetically?

    Also, do these formulas require the "Ctrl-Shift-Enter" method of entry to search the whole of Column A and provide responses in the corresponding rows of column B?

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    None of the formulas suggested by circledchicken or me require CTRL+SHIFT+ENTER

    just put the formula in the first row and copy/fill down the column

  6. #6
    New Member
    Join Date
    Jan 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    Quote Originally Posted by barry houdini View Post
    None of the formulas suggested by circledchicken or me require CTRL+SHIFT+ENTER

    just put the formula in the first row and copy/fill down the column
    Ha oh boy. Well I wanted to do this as an array formula, so I used

    =IFERROR(IF(A2:A7 = LOOKUP(A2:A7, D2:D4), "PCG", ""), "") CTRL+SHIFT+ENTER

    And it worked perfectly. Except now I can no longer sort the names alphabetically because it's part of the array. Maybe I should try your way with a "new" formula for each row.

  7. #7
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I'm guessing this is a basic IF formula question...

    Quote Originally Posted by bg61380 View Post
    ...Except now I can no longer sort the names alphabetically because it's part of the array. Maybe I should try your way with a "new" formula for each row.
    You should still be able to sort the lookup list in column D (the list in column A doesn't need to be sorted).
    But in any case, as barry houdini mentioned, you don't need the array structure - its just complicating things.

    If for some reason you want a single static formula solution, better options you can try include:
    -> using a Data Table, where Excel will automatically populate the result cells with the {=TABLE(,A1)} type syntax
    -> use a smart table with the associated structured references, so that your formula would change to a static form something like:
    Code:
    =IFERROR(
        IF([@[Original list]] = LOOKUP([@[Original list]], D$2:D$4), "PGC", ""),
        "")
    For more on data tables, see for example:
    How to use Microsoft Excel data tables to analyze information in a database
    Data Tables & Monte Carlo Simulations in Excel A Comprehensive Guide | Chandoo.org - Learn Microsoft Excel Online

    For more on smart tables, see for example:
    https://office.microsoft.com/en-us/e...010155686.aspx
    Working with Tables in Excel 2013, 2010 and 2007

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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