+ Reply to Thread
Results 1 to 8 of 8

Use Index Match With Choose To Return Text Data from different sheets

  1. #1
    Registered User
    Join Date
    09-29-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    Use Index Match With Choose To Return Text Data from different sheets

    I have a quandary and I am unsure how to fix it ... I am creating an asset management spreadsheet that I want to make as "stupid proof" and easy to use as humanly possible. My sub contractors have to be able to do minimal work to add their data, which, when compiled, will easily reach over 10000 rows of information.

    I have compiled, in one excel file, much of the basic data that I need to show up in the main spreadsheet to be populated. I have populated some of the rows with simple Data Validation lists, but there are simply far too many options to offer in these lists for all of the data. Scrolling through a hundred items to find the one item in the list you would like will take far too long. I have used vlookups to link several pieces of data but vlookup just isn't cutting it for some of the more complicated functions I want to add. I have tried to use index, match but something I am doing just isn't jiving.

    It seems to me that I SHOULD be able to use a combination of either vlookup and choose, index, match (or index, match, match) or some combination of these things to get my spreadsheet to return the information I need automatically, but I can't figure out how on earth I need to combine these equations to make it happen.

    SO, long story short, I have my main spreadsheet. I have a Data Validation drop down list where you can choose a certain code. Each code is associated with several attributes which are listed in a table on a different sheet. When someone chooses a particular code from the drop down, I want those attributes from the other sheet to populate into my main spreadsheet automatically.

    It's getting to crunch time and I'm supposed to be submitting a draft of this spreadsheet by end of day TOMORROW and I am at my wit's end here.

    I'm reasonably certain that what I want to do is possible, but I'm out of time to tinker with it myself ... Please help!!

  2. #2
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Use Index Match With Choose To Return Text Data from different sheets

    See the attachment and let me know if you are trying to achieve this.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-29-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: Use Index Match With Choose To Return Text Data from different sheets

    This should help. I had to alter my file some for confidentiality but nothing I excluded should affect what I'm asking so I've attached it here.

    In the attached file, Column D (Asset Code Type) is a Validated Data dropdown list from the Sheet entitled Asset Type Code List. Column E is linked to the data in Column D via an IF statement with a Vlookup and returns data from the Asset Type Code List sheet in the cells adjacent to the cell that matches what was chosen in Column D. That's fine. The list is long but I couldn't think of a better way to do that. If anyone knows of a better way, I would love to learn about it!

    To clarify what I'm asking, if I select "Compressor" from the drop down in the Asset List sheet in Column D, Column E automatically populates to "Compressor" because that is the asset type description that matches the drop down selection. When I select "Compressor" from the drop down, I also want all of my Primary Asset Attributes to populate from the sheet Asset Attributes as well. In this case, if I select Compressor in Column D, I want Columns I through P to read, TYPE, Name, CAPACITY, L/s, POWER, kW, VOLTAGE, V. (there may not be a way to return all of that data at the same time ... I'm not sure).

  4. #4
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Use Index Match With Choose To Return Text Data from different sheets

    Write this formula on I4 :
    Please Login or Register  to view this content.
    Now copy up to column P, then copy them down.
    Last edited by sanram; 09-29-2016 at 09:42 PM.

  5. #5
    Registered User
    Join Date
    09-29-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: Use Index Match With Choose To Return Text Data from different sheets

    That worked! Thank you! Can you explain how you made that formula work? The things I was coming up with were similar to that, but I didn't think to start with an IFERROR function for whatever reason ...

  6. #6
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Use Index Match With Choose To Return Text Data from different sheets

    This part
    Please Login or Register  to view this content.
    is getting the row number from Asset Attributes sheet where the the Asset Code Type is a same as D4 of Asset List sheet. Then
    Please Login or Register  to view this content.
    is indexing the value from that row of column D from Asset Attributes sheet. If D4 is blank or D4 don't match with any value of column A on Asset Attributes sheet then the 1st part will encounter an error. That's why I have used
    Please Login or Register  to view this content.
    . Now it will show blank in case of error.

  7. #7
    Registered User
    Join Date
    09-29-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: Use Index Match With Choose To Return Text Data from different sheets

    Thank you! That makes sense now! Now, how do I mark this as being solved??

  8. #8
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Use Index Match With Choose To Return Text Data from different sheets

    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. Replies: 2
    Last Post: 05-27-2015, 12:45 AM
  2. Replies: 3
    Last Post: 06-17-2013, 01:09 PM
  3. Replies: 3
    Last Post: 05-08-2013, 02:10 PM
  4. Trying to use match and index but return a text not numercial answer
    By parrot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2013, 10:44 AM
  5. [SOLVED] Two Excel sheets trying to match text data and return the match from the second column!
    By bankcott in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-02-2013, 02:17 PM
  6. [SOLVED] VLOOKUP, INDEX, MATCH... What to choose
    By Piloulondon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  7. [SOLVED] VLOOKUP, INDEX, MATCH... What to choose
    By Piloulondon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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