Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Match and Insert Name (2000)

    Hello Loungers,

    I have a spreadsheet broken up into 2 halves. First half, has account information, with account number in column b. On second half of spreadsheet, I have account numbers in column o and account names in column p. I'd like to be able to add a column next to b and add the account name here when there is a match between the account numbers in column b and column o. I tried the following formula and it doesn't work: =IF(B2=$O$2:$O$900, P2, "No Match").

    Any suggestions?

    All help appreciated!

  2. #2
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Match and Insert Name (2000)

    Solved my own problem using this Vlookup formula: =VLOOKUP(B4,$P$1:$Q$84,2,FALSE).

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Match and Insert Name (2000)

    If there is a possibility that there won't be a match, you might want to change that formula to:

    <pre>=IF(ISERROR(VLOOKUP(B4,$P$1:$Q$84,2,FALSE))," No Match",VLOOKUP(B4,$P$1:$Q$84,2,FALSE))
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Match and Insert Name (2000)

    Legare, thanks! For informational purposes, can you please explain the formula to me?

    Take care,

    Mitch

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Match and Insert Name (2000)

    The formula checks to see if the result of the VLOOKUP is an error (indicating no match). If the result was an error, then the IF returns "No Match". If the result was not an error, the the IF returns the VLOOKUP value. It might be better to use ISNA instead of ISERROR since ISERROR will cause "No Match" to be displayed when any error occurs. When it can't find a match, VLOOKUP returns a #NA error. If you use ISNA instead of ISERROR, the other errors, like #REF, will still display as errors.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find Match and Insert Name (2000)

    =IF(ISERROR(VLOOKUP(B4,$P$1:$Q$84,2,FALSE)),"No Match",VLOOKUP(B4,$P$1:$Q$84,2,FALSE))

    VLOOKUP(B4,$P$1:$Q$84,2,FALSE)
    will "lookup" the value in cell B4 in the range P1:P84. Since the 4th parameter is FALSE, it must be an exaact match and excel will look starting in P1 and go down the list until if finds an exact match for the value in B4. If it finds one it will get the value from col Q in that row (Q is the 2nd col in the PQ, P is col 1). If it does not find an exact match it yields an error.

    ISERROR(VLOOKUP(B4,$P$1:$Q$84,2,FALSE))
    Will give a TRUE if the Vlookup yields an error (no match) or FALSE (match) when there is no error

    =IF(ISERROR(VLOOKUP(B4,$P$1:$Q$84,2,FALSE)),"No Match",VLOOKUP(B4,$P$1:$Q$84,2,FALSE))
    The IF checks the "iserror", if there is an error (TRUE, ie no match) it puts the text "No Match" in the cell, if it is not an error (FALSE, yes a match) it displays the value found in column Q that matches the row.

    Steve

Posting Permissions

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