+ Reply to Thread
Results 1 to 10 of 10

Excel 2010 - INDEX formula issues

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Excel 2010 - INDEX formula issues

    Hi all,
    I'm new to this and I'm having a lot of problems trying to work out if I'm using the right formula. I'm trying to use the index formula but with no success. I'm trying to gather certain data using a "Supplier ID". If I change the Supplier ID I would like the table to reflect the changing values in the Results Table.
    I have attached the spreadsheet and data. If anyone can give me an idea of what to do that would be greatly appreciated. If possible, 1 formula for 1 column of data would be greatly appreciated. Then I can work out how to do the others.

    Your help would be greatly appreciated!!
    Regards,
    Sarah
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Excel 2010 - INDEX formula issues

    In B6 Cell of Results Sheet - ARRAY FORMULA - REQUIRES CTRL+SHIFT+ENTER

    =IFERROR(INDEX('Data Sheet'!$A$2:$R$15,SMALL(IF('Data Sheet'!$A$2:$A$15=$C$2,ROW('Data Sheet'!$A$2:$A$15)-ROW('Data Sheet'!$A$2)+1),ROW($A1)),MATCH(B$5,'Data Sheet'!$A$1:$R$1,0)),"")

    Drag it down and right...

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    Hi Sixth Sense,
    Thank you so much for your prompt assistance.
    I will give it a try and let you know

    Many thanks,
    Sarah

  4. #4
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    Quote Originally Posted by :) Sixthsense :) View Post
    In B6 Cell of Results Sheet - ARRAY FORMULA - REQUIRES CTRL+SHIFT+ENTER

    =IFERROR(INDEX('Data Sheet'!$A$2:$R$15,SMALL(IF('Data Sheet'!$A$2:$A$15=$C$2,ROW('Data Sheet'!$A$2:$A$15)-ROW('Data Sheet'!$A$2)+1),ROW($A1)),MATCH(B$5,'Data Sheet'!$A$1:$R$1,0)),"")

    Drag it down and right...

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
    Hi Sixthsense,
    I tried what you suggested and the first line was correct but then no other information appeared when I dragged the formula down and to the right. Also when I changed the Supplier ID to "19", the data associated was incorrect.
    Can you have a look at the spreadsheet attached
    Regards,
    Sarah
    Attached Files Attached Files

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Excel 2010 - INDEX formula issues

    You have entered the formula as normal with enter...

    But it is an ARRAY FORMULA which requires CTRL+SHIFT+ENTER. Please pay some attention for the text we quote... Even I highlighted some text with colors....

  6. #6
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    OK I will try again

  7. #7
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    Fantastic!!! It worked!!
    Thank you very much for your help. I've been at it for days

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Excel 2010 - INDEX formula issues

    Glad it helps you and thanks for the feedback

  9. #9
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    Oh, how do I mark this as "solved"?

  10. #10
    Registered User
    Join Date
    08-12-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Excel 2010 - INDEX formula issues

    It's ok! I've done it

+ 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. Excel 2010 Scrolling Issues
    By gasguzzler in forum Excel General
    Replies: 6
    Last Post: 12-30-2014, 08:09 AM
  2. [SOLVED] Excel 2010 upgrade glitches & issues.
    By witham in forum Excel General
    Replies: 2
    Last Post: 11-21-2014, 05:28 AM
  3. [SOLVED] Treeview Issues in VBA/Excel 2010
    By XanDC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2013, 12:47 PM
  4. Memory Issues Excel 2007 vs. 2010
    By always_learning in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-11-2012, 04:00 PM
  5. Excel 2010 timing issues using Dir
    By brifoz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-05-2012, 11:52 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