+ Reply to Thread
Results 1 to 13 of 13

VLookup formula not working

  1. #1
    Registered User
    Join Date
    05-05-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    VLookup formula not working

    Hi,
    I'm new to VLOOKUP formulas, and trying to get this to work.
    =VLOOKUP($E4;Data;2;FALSE)
    Capture.PNG

    Basically, I want 'mary@email.com' to appear in F1, beside 'Mary 1023', and 'john@email.com' to appear in F2, beside 'John'. I'm trying to get it to get a name from column E and look for a match in column J (doesn't have to be an exact match, as there are codes after some names in column E, although, a trim function would be handy to just get the names), and when a match is found, to put the corresponding value from column K (E-Mail Address) into the adjacent cell in column F. How do I do that?

    All I'm getting is the actual formula in the cell, all the way down.

    Thanks!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: VLookup formula not working

    Is the format of the cell(s) with the formulae set as text? Reformat as general.

    if that;s not it...Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,892

    Re: VLookup formula not working

    You define name as "Data". Confirm about range. If you share sample file its more conviniant.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Registered User
    Join Date
    05-05-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup formula not working

    Thanks. Attaching test data. In my original file, I had defined the range as 'Data'. The range in the sample file is from F1:G10.
    I want the email addresses from column G to appear in column B, beside their matching name (don't have to match the 4-digit codes after the names).

    Some of the names in column A don't have a match in column F, but that's ok. I just want the matching ones to have their cell in column B poopulated.
    Attached Files Attached Files

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLookup formula not working

    based on your picture
    try in F2
    =IFNA(INDEX($E$2:$E$10,MATCH(IFERROR(MID(A2,1,SEARCH(" ",A2)-1),A2),$D$2:$D$10,0)),"")
    change references accordingly

    or
    with your test file:
    =IFNA(INDEX($G$2:$G$10,MATCH(MID(A2,1,SEARCH(" ",A2)+1),$F$2:$F$10,0)),"")
    or
    if there is more than one space in the name
    =IFNA(INDEX($G$2:$G$10,MATCH(TRIM(MID(A2,1,SEARCH(" ",A2)+1)),TRIM($F$2:$F$10),0)),"")
    as array formula (commit by Control+Shift+Enter)
    Last edited by sandy666; 05-05-2017 at 09:03 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: VLookup formula not working

    Per your file
    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    1
    Name E-mail Address Created On Name E-Mail Address
    2
    Atest B 1203
    2/11/2015 17:02
    Aidan S Aidan.S@test.com
    3
    Aidan M 1283
    2/11/2015 17:02
    Alan K Alan.K@test.com
    4
    Aidan S Aidan.S@test.com
    8/5/2016 9:21
    Alan S Alan.S@test.com
    5
    Aideen A 1260
    2/11/2015 17:02
    Albert B Albert.B@test.com
    6
    Aiden O 1335
    2/11/2015 17:02
    Andrew D Andrew.D@test.com
    7
    Alan C
    2/11/2015 17:02
    Andrew M Andrew.M@test.com
    8
    Alan K Alan.K@test.com
    11/18/2015 16:05
    Anne F Anne.F@test.com
    9
    Albert B Albert.B@test.com
    2/11/2015 17:02
    Anne T anne.t@test.com
    10
    Andrew M 1301 Andrew.M@test.com
    2/11/2015 17:02
    Aoife O Aoife.O@test.com
    Sheet: Sheet1
    Last edited by AlKey; 05-05-2017 at 09:20 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    08-15-2014
    Location
    Denmark
    MS-Off Ver
    365 Pro
    Posts
    77

    Re: VLookup formula not working

    Or try this into B2:

    =IFERROR(VLOOKUP((LEFT(A2,(FIND(" ",A2,1)+1))),$F:$G,2,FALSE)," ")
    Edit: Was a bit too slow, AlKey posted a formula like mine :-P
    Last edited by chriskay; 05-05-2017 at 09:20 AM.

  8. #8
    Registered User
    Join Date
    05-05-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup formula not working

    Thank you so much! I used AlKey's suggestion and it worked.

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLookup formula not working

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  10. #10
    Registered User
    Join Date
    05-05-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup formula not working

    Now that I have the email addresses in the correct column, I would like to delete the 2 columns form the Data table, but that removes the results of the VLookup. Is there any way to keep the text but remove the Data table? I tried formatting the cells as text, but that didn't work. I need to use the resulting spreadsheet, but without the data columns.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLookup formula not working

    Copy F column (all data there) or this column where you got result (eg. B), and Paste in the same column As Value
    then delete what you want

  12. #12
    Registered User
    Join Date
    05-05-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup formula not working

    Thanks for all the help, that worked.

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: VLookup formula not working

    You are welcome

+ 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. Why is this VLOOKUP formula not working?
    By mfhaq77 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2016, 12:34 AM
  2. VBA Vlookup formula not working
    By jasonmcasey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2014, 06:22 AM
  3. [SOLVED] Vlookup formula not working
    By DKAbi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2012, 10:01 AM
  4. Excel 2007 : Vlookup formula not working
    By jana1120 in forum Excel General
    Replies: 2
    Last Post: 05-16-2012, 08:19 AM
  5. Vlookup Formula not working...
    By Swambo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-15-2011, 05:31 PM
  6. Vlookup formula not working
    By rbwen in forum Excel General
    Replies: 7
    Last Post: 12-08-2011, 08:12 PM
  7. Vlookup formula not working
    By RightPlace in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-07-2011, 11:38 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