+ Reply to Thread
Results 1 to 16 of 16

if Account number = return Account Name

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    if Account number = return Account Name

    My apologies if this has been addressed previously however, if it is in the forum, feel free to direct me. TY.

    I recall Excel being able to do this but I can't remember the formula and I am tired of hitting my head on the desk, cause it's starting to hurt a bit.

    Here is my case. I have a column that I enter account numbers in. In the next cell, I want it to search my other worksheet within my workbook for the account number and populate the account name.

    I know I used to be able to do this but for the life of me, I can't recall the formula. I believe it had "if" and "vlookup" but seem to get the formula to work.

    Please advise.

    Thank you in advance.

    excel_help.xlsx
    Last edited by mom2carisa; 09-04-2013 at 12:07 PM.

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: if Account number = return Account Name

    try this in column B.....it will have to copied into all the cells for column B....

    Please Login or Register  to view this content.
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: if Account number = return Account Name

    For Row 551

    =INDEX(Account!C:C,MATCH(C551,Account!B:B,0))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: if Account number = return Account Name

    Is this what you want?

    =VLOOKUP(C2,Account!$B$1:$C$131,2,FALSE)

    Windy

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,319

    Re: if Account number = return Account Name

    =VLookup(C551,Account!$B$1:$C$131,2,0)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    09-04-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if Account number = return Account Name

    @ Ace...I think your method would work if I didn't need the account numbers but it was a GREAT suggestion! - this list, is a call log. When I get a call and the company provides me an account number or a property name, I want it to automatically populate either or that way, I can log the call time/date and issue. I work for a fire protection company and let's face it, paperwork is NOT conducive to a fire prevention company! Digital is my method and I am trying to digitalize everything to avoid, "if there is a fire, which do you choose" scenario.

    @windy & @oeldere, THANK YOU! I recalled I had an "if n/a" scenario but this is totally going to make my life so much easier! I see what I was doing wrong. I was not selecting the "cells that contain the account number" under messages but the COLUMN itself and since Account# is the column...well of course this should cause the problem then...for some odd reason I recalled selecting the entire column but now I see that it is "from this cell to this sell, I want you to search 'account sheet' under this column and return if found, this account name"....THANK YOU! I knew you all would have the answer! You guys really knocked this one out!

    So not losing this formula! So many uses!

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,319

    Re: if Account number = return Account Name

    Please Login or Register  to view this content.
    Since you use excel 2007

    iferror(VLookup(C551,Account!$B$1:$C$131,2,0),"")

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,319

    Re: if Account number = return Account Name

    @mom2carisa

    Please also respond on the solution of judgeh59 in #2.

  9. #9
    Registered User
    Join Date
    09-04-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if Account number = return Account Name

    @judgeh59 - I tried your formula but it seems when I enter the account number, it pulls up the account after the one I entered (like it is adding a +1 after??

    for example: 513784 = sherbourne hall, 513785=1412 Colby (and it's showing up as 513786), and 513720=motor and palms (and it's showing up as Lofts at laurel which should be 513786). Dunno what would cause that jump ahead but, I'm also on Dayquil.

    Thank you all very much for your quick replies and solutions!

  10. #10
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: if Account number = return Account Name

    seems to work fine on your file...you do have to change the C571 to C551 when you plug it in the cell and copy down....sorry if I confused it....

  11. #11
    Registered User
    Join Date
    09-04-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if Account number = return Account Name

    Ahh....ok....I see.....now I get it! :O)

  12. #12
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: if Account number = return Account Name

    glad we could all help....I pretty much live in IFERROR(VLOOKUP area....have fun

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,319

    Re: if Account number = return Account Name

    Please Login or Register  to view this content.
    Advice:

    don't use whole columns for search or lookup.

    if you use excel 2007 it needs to look at all rows (> 1 mln) => this can slow your system.

    Use a range that always fits e.g. B5000:E5000.

  14. #14
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: if Account number = return Account Name

    good to know....I'm on 2010 and I have not noticed any difference in speed....but good to know....one reason I do that is that if you add data to the lookup area it picks it up....of course, if you make a named range it limit the size and still get it done....thank you for the info....

  15. #15
    Registered User
    Join Date
    09-04-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: if Account number = return Account Name

    Ahhh! @oeldere! I was looking at the column not the cells! Thank you for pointing out my "duh" moment! I would love to install 2010 but I don't think this computer can take any more from my abuse of over working the system!

    Again, many thanks to everyone that provided solutions!

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,319

    Re: if Account number = return Account Name

    Thanks for the reply.

    Glad I could help.

    You can add rep(utation)points to the members who helped you by clicking on the star * on the left side.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return Account Nickname if Account Number Matches
    By spacle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-03-2013, 11:04 AM
  2. [SOLVED] How to censor account numbers based on how many characters are in the account number
    By Mcorydon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 01:06 PM
  3. [SOLVED] Return list of unique account numbers
    By trumpetman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-17-2012, 06:42 PM
  4. Replies: 1
    Last Post: 09-07-2012, 12:00 PM
  5. Replies: 9
    Last Post: 03-12-2012, 05:30 AM

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