+ Reply to Thread
Results 1 to 7 of 7

Formula or macro that looks up matches data, returns the matched value

  1. #1
    Registered User
    Join Date
    02-04-2016
    Location
    Harare
    MS-Off Ver
    OFFICE 2010
    Posts
    8

    Formula or macro that looks up matches data, returns the matched value

    GOOD DAY. I am a dummy in excel and programming. in the workbook i have attached, in Template! $G2 i want that when a user enters 1, the formula looks up the value for 1 in LISTS!B7 and replaces the 1 with the value in B7. same goes for if users enters 2, 3, 4, 5. if any other number it should give an error message.

    I would really appreciate your help on this

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    Re: Formula or macro that looks up matches data, returns the matched value

    a vlookup or index match formula would usually accomplish something like that, the problem is that you cannot have both a value "1" and a formula in a cell at the same time.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    02-04-2016
    Location
    Harare
    MS-Off Ver
    OFFICE 2010
    Posts
    8

    Re: Formula or macro that looks up matches data, returns the matched value

    I have tried the Vlookup formula, is not working what i want. perhaps there is no such way to achieve this. i will have to try and learn programming

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,691

    Re: Formula or macro that looks up matches data, returns the matched value

    Hey there,
    Right clcik "TEMPLATE" tab>>view code>> paste this code there-
    Please Login or Register  to view this content.
    Check attached.
    Try entering a number in column G.
    Attached Files Attached Files
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  5. #5
    Registered User
    Join Date
    02-04-2016
    Location
    Harare
    MS-Off Ver
    OFFICE 2010
    Posts
    8

    Re: Formula or macro that looks up matches data, returns the matched value

    thank You this part is solved. I am studying the macro now

    2. on the same worksheet is it possible to have a formula that searches in another workbook for an exact match of B2 and returns the ID number in C2

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,691

    Re: Formula or macro that looks up matches data, returns the matched value

    You can use Vlookup for the same.
    Check this link - http://www.extendoffice.com/document...-workbook.html
    This shows you how you can use vlookup between 2 workbooks.
    If you face any problems in implementing the formula do ask.

  7. #7
    Registered User
    Join Date
    02-04-2016
    Location
    Harare
    MS-Off Ver
    OFFICE 2010
    Posts
    8

    Re: Formula or macro that looks up matches data, returns the matched value

    Thank you sourabhg98. i have used the following formula
    =IFERROR(VLOOKUP(TRIM($B2),'[Activity Attendance Register Template v3 Blank.xlsx.xlsm]Registration'!$A$2:$J$24559,1,FALSE),VLOOKUP(MID(TRIM($B2)&" "&TRIM($B2),FIND(" ",TRIM($B2))+1,LEN(TRIM($B2))),'[Activity Attendance Register Template v3 Blank.xlsx.xlsm]Registration'!$A$2:$J$24559,1,FALSE))

    but it returns a #N/A

+ 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. COUNTIFS issue - Returns zero matches (possibly wrong formula???)
    By sx200n in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2014, 08:09 AM
  2. [SOLVED] macro to filter matched data
    By severet93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2014, 07:39 AM
  3. [SOLVED] search for specific data in two columns and if it matches returns an ID number
    By ncaravela in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2013, 01:48 PM
  4. Counting matched values from another sheet with concatenate always returns zero
    By JerkyTreats in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 06:51 PM
  5. [SOLVED] VBA replacing a formula with a macro returns #Value (Wrong data type)
    By silent3486 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-14-2013, 04:17 PM
  6. Help with Macro/formula - Lookup that returns Dropdown List of matches
    By alfykunable in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 12:43 AM
  7. [SOLVED] SUMIFS only returns the 1st matched value in my range of criterias
    By catzilla in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-30-2012, 11:55 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