+ Reply to Thread
Results 1 to 16 of 16

INDIRECT(MATCH...) Formula pulling into correct place but not correct value

  1. #1
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Unhappy INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    The formula on the data tab of the attached spreadsheet pulls the information from the Qty, vatable, and price columns on the items tab. I need the data to move from 1 row per item on items tab to 1 row per sale reference number on the data tab. The formula works so far, it is pulling into the correct column and row but it is not pulling in the correct value. Can anyone please tell me where I have gone wrong.

    I apologise - original attachment had a corrupted formula in it. new revised one attached
    Attached Files Attached Files
    Last edited by carrach; 11-24-2014 at 07:13 AM. Reason: formula corrupted in attachment

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    it's not a very good idea to use widely INDIRECT. named ranges, INDEX and MATCH are enought.

  3. #3
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    I was unable to find an index match formula that would do the job and not slow down the spreadsheet so that it would freeze. I have since added in the helper column so unsure if an index match could be used with the helper column. There are 100000+ rows in the full spreadsheet, not all rows in the data tab will have a match in the items tab. If index match will work please can I have an example that will not freeze up my spreadsheet. Many thanks

  4. #4
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    346

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    if you change your formula in E5 to and copy both ways

    =IFERROR(INDIRECT("items!"&E$2&MATCH(E$4,INDIRECT($D5),0)+MATCH($A5,items!$A$8:$A$15,0)+6),"")

    that should work.

    that change your reference cell for INDIRECT related to your start row rather than fixed to +7
    Last edited by AlanY; 11-24-2014 at 07:50 AM.

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    all strings must be put down the same way. Best way is using validation lists.

  6. #6
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Unhappy Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Replied with quote:
    Last edited by carrach; 11-24-2014 at 09:52 AM.

  7. #7
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Thank you very much for this, however it seems to be pulling into the wrong column although all the correct values are there they seem to be 1 column over from where they should be.



    Quote Originally Posted by AlanY View Post
    if you change your formula in E5 to and copy both ways

    =IFERROR(INDIRECT("items!"&E$2&MATCH(E$4,INDIRECT($D5),0)+MATCH($A5,items!$A$8:$A$15,0)+6),"")

    that should work.

    that change your reference cell for INDIRECT related to your start row rather than fixed to +7

  8. #8
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Thank you for your help, however I tried this formula before and it froze the spreadsheet. It ran the formula for hours before freezing. I dont think that this is the best option for a spreadsheet with over 100000 rows and 113 columns with this formula in every cell and I am reluctant to try it again as I am sure you will understand. If there is a way of using this simple index match that will not freeze the spreadsheet then I would be keen to try that.



    Quote Originally Posted by tim201110 View Post
    all strings must be put down the same way. Best way is using validation lists.

  9. #9
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    346

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    not quite sure i got it.

    could you highlight the problem on the attached
    Attached Files Attached Files

  10. #10
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    it will be faster to use vba and arrays. But a pair of INDEX and MATCH without {} and with a source table of 6x100 is the fastest way in worksheet functions. And this pair is very clever and isn't volatile, It can't frooze excel app. Look at your hardware

  11. #11
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Smile [SOLVED] Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Hi Alan,
    So sorry, I must have done something wrong as it seems to be working fine now - so a huge thankyou. I will now try it in my full size spreadsheet and see what happens

    Quote Originally Posted by AlanY View Post
    not quite sure i got it.

    could you highlight the problem on the attached

  12. #12
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    346

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Quote Originally Posted by carrach View Post
    Thank you for your help, however I tried this formula before and it froze the spreadsheet. It ran the formula for hours before freezing. I dont think that this is the best option for a spreadsheet with over 100000 rows and 113 columns with this formula in every cell and I am reluctant to try it again as I am sure you will understand. If there is a way of using this simple index match that will not freeze the spreadsheet then I would be keen to try that.
    i notice that on your data(2) sheet, for A10072 you have 2nd class post instead of 1st class on the items sheet

  13. #13
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    346

    Re: [SOLVED] Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct val

    Quote Originally Posted by carrach View Post
    Hi Alan,
    So sorry, I must have done something wrong as it seems to be working fine now - so a huge thankyou. I will now try it in my full size spreadsheet and see what happens
    no problem, good luck on that

  14. #14
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: [SOLVED] Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct val

    Quote Originally Posted by AlanY View Post
    no problem, good luck on that
    Unfortunately the calculations are too big for my excel to handle so I am having to start again with finding a way to do this. It works in the example spreadsheet but my original is obviously way too big.

  15. #15
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Quote Originally Posted by tim201110 View Post
    it will be faster to use vba and arrays. But a pair of INDEX and MATCH without {} and with a source table of 6x100 is the fastest way in worksheet functions. And this pair is very clever and isn't volatile, It can't frooze excel app. Look at your hardware
    Hi tim,
    I would like to give the INDEX MATCH another try but not sure what you mean by source table of 6 x 100 - how does this relate to my spreadsheet example?

  16. #16
    Registered User
    Join Date
    09-04-2009
    Location
    Gloucestershire, England
    MS-Off Ver
    Excel 2010
    Posts
    45

    Re: INDIRECT(MATCH...) Formula pulling into correct place but not correct value

    Quote Originally Posted by tim201110 View Post
    all strings must be put down the same way. Best way is using validation lists.
    Hi Tim,
    tried to use your index match formula but it takes over 15 minutes to process in just 1 single column and I have 120+ columns of data that need to process. Therefore this is not the best option but thank you anyway

+ 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: 3
    Last Post: 07-01-2013, 07:50 PM
  2. [SOLVED] Formula not pulling the correct data based on a drop down list
    By pleasesmile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 10:46 AM
  3. Indirect function formula not correct
    By trobie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2013, 08:30 AM
  4. Index Match not pulling in correct info
    By jeremiahbret in forum Excel General
    Replies: 1
    Last Post: 02-07-2012, 03:48 PM
  5. LOOKUP formula not pulling correct data
    By anelson02 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2009, 06:37 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