+ Reply to Thread
Results 1 to 9 of 9

Creating a vlookup or match/index?

  1. #1
    Registered User
    Join Date
    11-24-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Creating a vlookup or match/index?

    Hi all I am really struggling with creating a formulae. Wondering if anyone had any ideas?

    What I am trying to do:

    Based on asset tag column in sheet 1 bring in the expiry date of an asset from column 2 in sheet 2. The common element or index being in column 1 (asset tag). I need to do this as I have several thousand assets in one sheet already edited and need to bring in expiry date of assets from a separate sheet. I have attached a test spreadsheet.

    Thanks
    Attached Files Attached Files
    Last edited by jgar757; 11-24-2017 at 11:59 AM.

  2. #2
    Registered User
    Join Date
    11-24-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a vlookup or match/index?

    Please see attached test.
    Last edited by jgar757; 11-24-2017 at 11:56 AM.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Creating a vlookup or match/index?



    To attach an Excel file to your post,
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Creating a vlookup or match/index?

    Try

    =IFERROR(VLOOKUP(C2,Sheet2!$A$2:$B$44,2,0),"")

  5. #5
    Registered User
    Join Date
    11-24-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a vlookup or match/index?

    Unfortunately came up as blank.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Creating a vlookup or match/index?

    It will as most of the data does match as it not in a consistent format .

    Change the Asset tag columns in both sheets to TEXT.

  7. #7
    Registered User
    Join Date
    11-24-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a vlookup or match/index?

    Hi ive set both asset fields to text and put your formulae in. Unfortunately the warranty expiry date hasn't been 'imported' to column E on sheet 1. Thanks for your help !
    Attached Files Attached Files
    Last edited by jgar757; 11-24-2017 at 12:35 PM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: Creating a vlookup or match/index?

    See attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-24-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: Creating a vlookup or match/index?

    Thanks genius!

+ 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. Creating INDEX MATCH MATCH formula based off text in cells
    By bbkdude in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-26-2017, 10:37 AM
  2. VLOOKUP or INDEX/MATCH with multiple column index numbers
    By cerebral87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2017, 07:13 PM
  3. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  4. Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  5. [SOLVED] Creating a Dashboard with Vlookup (or Match/Index)
    By rstarr1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 02:06 PM
  6. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  7. [SOLVED] VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row
    By bisaya789 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 03:46 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