+ Reply to Thread
Results 1 to 13 of 13

Index and Match to display data from another list

  1. #1
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Index and Match to display data from another list

    I am working on a staff spreadsheet to track online training progress.
    Attached is a identified version.
    In Column AI on the main tab Post Bulk Upload i would like to return the results from Column D (Training status from a daily report) of tab Adobe Report. The main identifier is the staff number, taken from Column A on Post Bulk Upload .
    So the aim is... take Staff number on main tab, find it on report tab and return the related training status result and display it on the main tab.
    Like i have written in Column AK.

    I used the following formula without working. I am sure it is something simple i am missing.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Index and Match to display data from another list

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    Unfortunately I had tried that and i receive the #N/A error.
    The data in column A is formatted as "Text" whereas every other column in the formula is formatted "General"
    Do you think this is an issue?

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Index and Match to display data from another list

    You must have made a mistake....
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    Unfortunately for some reason the code works perfectly in the deidentified spreadsheet, but when copied directly into the live spreadsheet it throws the #N/A error.
    Attached are two screenshots from the live spreadsheet. I have confirmed a user is on the Adobe Report as completed training, and is on the Post Bulk Upload tab.

    When i removed the login ID from the Adobe tab in the deidentified spreadsheet the error was thrown. It appears that when searching the Adobe tab for the login ID in the live spreadsheet, Excel is unable to properly locate the ID.

    Any suggestions??
    Attached Images Attached Images

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index and Match to display data from another list

    Cant really even see the 2nd screen shot, and we cant really work with pictures anyway.

    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    See my first post for the clean sample.
    The reply from Glenn Kennedy include the formula which works in his attached spreadsheet, but does not when copied directly to the live spreadsheet.
    The screen shots are supplied to provide a view of what is occurring in the live spreadsheet, of which is too large to upload into ExcelForums.
    Attached Images Attached Images

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index and Match to display data from another list

    Sorry, Im not working with pictures, they cannot show underlying details of cell contents and formatting.

    If you could upload a sample of your workbook, with all sensitive info removed/changed, I will take a look for you

  9. #9
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    See my first post for the clean sample.
    Which, as I have said, works as expected with the formula from Glenn Kennedy
    When i copy raw text from the real spreadsheet to the one supplied by Glenn Kennedy I suddenly receive the #N/A error again.
    I have tried to sort the order of each column associated with the formula numerically but still no luck. Not all IDs from the Post Bulk Upload tab are in the Adobe Report tab.
    The formula works as it should without my raw data but the picture demonstrates I have copied the formula as it was supplied to me yet the error has been thrown. As stated, this is to show what is the result is, it is not expected to be used to work from. The real spreadsheet is too large to upload and I have already provided a desensitized one.
    I feel there is something limiting the formula from interpreting either the volume of entries or the sort order.

    Is there an alternative to Index and Match to extract data?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index and Match to display data from another list

    This is exactly why I wanted to see a (clean) sample of your actual data. Apparently, something is different there, compared to your 1st set of dummy info. Without seeing some realistic data. it's hard to say what the problem might be

  11. #11
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    For privacy I have had to strip quite a bit of data from it. This has removed the visual effects of my conditional formatting which must remain in the final real spreadsheet.
    I have tried sorting the ID & Employee number from smallest to largest, yet on the Post Bulk Upload tab, 52808427 always appears at the top for some reason.
    This still results in the #N/A error being output
    If i copy the Employee numbers directly into the ID column as text, or vice versa the status is displayed as I want, yet obviously not correctly representing each employees true status but is proves to me the formula does work, it has something to do with either formatting of the cells or most likely the inability for randomly listed numbers to be handled by Index &/ Match.
    Attached Files Attached Files

  12. #12
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Index and Match to display data from another list

    in column A you have texts, which only look like numbers.
    while in Report sheet there are real numbers, so try:

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

  13. #13
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Index and Match to display data from another list

    That has worked, thank you so much. My first reply to you hinted i suspected this may be the case, but I did not know how to solve it. I will have to remember this.

+ 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. [SOLVED] INDEX MATCH in a List Data Validation
    By alaramee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2018, 01:21 PM
  2. Index Match formula does not look-up/display data all the time.
    By iceandfire in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-18-2014, 08:46 PM
  3. Index-Match Data Validation List not working
    By totally_lost in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-16-2014, 09:27 AM
  4. [SOLVED] Index-Match Data Validation List
    By DinghoAteMyBaby in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-22-2013, 10:42 AM
  5. Replies: 6
    Last Post: 01-23-2013, 02:21 PM
  6. Data Validation List Using Index & Match Formula
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-16-2012, 03:08 AM
  7. Replies: 1
    Last Post: 09-05-2012, 11:39 AM

Tags for this Thread

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