+ Reply to Thread
Results 1 to 15 of 15

Urgent help with formula please! Stronger index match

  1. 07-16-2017, 02:59 AM #1
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Urgent help with formula please! Stronger index match

    Hi everyone,

    Can you please help me with a formula in the sheeet attached. YOu can download the file from this link:

    https://we.tl/lWWWg3DWEs

    I am building a model and need help with a fomula that operates similar to an index match. In the sheet BD-01, this represents a zone. I will be having several zones similar to this. I need to find a fomula that matches and pulls L14 to L22 in the land budget tab (as E4 is BD-01) and pull that into E13 to E21 in the BD-01 tab. I need this dynamic enough where it matches the BD-01 since I will have all the way up to BD-14 and will have several tabs and will be using the formula to pull many pieces of data.

    Please let me know if any of the above is unclear and I can calrify further.

    Thanks
    Reply With QuoteRegister To Reply
  2. 07-16-2017, 03:01 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    Please attach your workbook directly here. Many members will not or cannot access file-sharing sites.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-16-2017, 03:06 AM #3
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Re: Urgent help with formula please! Stronger index match

    I've tried to upload it many times. It's not working and I've used many file types. I even zipped it. It keeps saying upload failed
    Reply With QuoteRegister To Reply
  4. 07-16-2017, 03:11 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    You need to cut the sample file down to just enough data to illustrate the problem you'd like solving - we don't need to see your entire dataset.

    PS Nobody should have to download a massive 18MB workbook in order to be able to help you!
    Last edited by AliGW; 07-16-2017 at 03:27 AM.
    Reply With QuoteRegister To Reply
  5. 07-16-2017, 04:05 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    Still waiting for a cut-down version of your workbook ...
    Reply With QuoteRegister To Reply
  6. 07-16-2017, 04:24 AM #6
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Re: Urgent help with formula please! Stronger index match

    See attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-16-2017, 04:42 AM #7
    Syrkrasi
    Syrkrasi is offline
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Urgent help with formula please! Stronger index match

    I hope this works for you... see attachment

    =INDEX('Land Budget'!$D$6:$D$14,MATCH(A4,'Land Budget'!$A$6:$A$14,0))

    Array Formula so press Shift + Ctrl + Enter keys simultaneously, then copy down(by dragging down) to last entry

    As for the Total, just use =SUM(D4:D12)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-16-2017, 04:44 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    In D4 copied down:

    =INDEX('Land Budget'!D:D,MATCH('BD-01'!A$1,'Land Budget'!C:C,0)+ROW())
    Reply With QuoteRegister To Reply
  9. 07-16-2017, 04:48 AM #9
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Re: Urgent help with formula please! Stronger index match

    Thank you so much

    I understand how the index match function works. Can you please explain the 'BD-01'!A$1 part of it and why you also put ROW()

    Thanks
    Reply With QuoteRegister To Reply
  10. 07-16-2017, 04:55 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    Yes.

    =INDEX('Land Budget'!D:D,MATCH('BD-01'!A$1,'Land Budget'!C:C,0)+ROW())

    This is the value that Excel uses as the lookup value for the array, so if you change cell A1 to BD-02, then the formula will automatically move down to BD-02 in the Land Budget sheet.

    =INDEX('Land Budget'!D:D,MATCH('BD-01'!A$1,'Land Budget'!C:C,0)+ROW())

    This makes sure that the value returned from the array matches the correct item on the list - it adds the row value (4 in the case of cell D4, 5 in the case of D5, and so on) - so Excel will find the correct line in the Land Budget data to return. If you change the values in the BD-02 section of the Land Budget sheet, and then change cell A1 on the other sheet to BD-02, you will see it working.
    Last edited by AliGW; 07-16-2017 at 04:59 AM.
    Reply With QuoteRegister To Reply
  11. 07-16-2017, 05:06 AM #11
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Re: Urgent help with formula please! Stronger index match

    Yes I completely understand thank you.

    However you will notice if i add a row then it changes the cell reference changes.

    You can see attached I plugged in the formula and added a few rows and you will it doesn't refer to the right cell. Can you please help me with this last step?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 07-16-2017, 05:09 AM #12
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    Yes. Either this:

    =INDEX('Land Budget'!D:D,MATCH('BD-01'!A$1,'Land Budget'!C:C,0)+ROW()-1)

    or this:

    =INDEX('Land Budget'!D:D,MATCH('BD-01'!A$1,'Land Budget'!C:C,0)+ROWS(A$1:A4))
    Reply With QuoteRegister To Reply
  13. 07-16-2017, 05:47 AM #13
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    Having pressed the forum with the urgency of your request and having opened a duplicate thread in your impatience that was against the forum rules, it would have been nice to have let us know if this had worked or not. In the absence of any response and as you have now gone offline, I am assuming that this thread is now solved.

    Have a nice day!
    Reply With QuoteRegister To Reply
  14. 07-16-2017, 08:05 AM #14
    targus92
    targus92 is offline
    Registered User
    Join Date
    10-23-2015
    Location
    USA
    MS-Off Ver
    Windows MS office 2010
    Posts
    52

    Re: Urgent help with formula please! Stronger index match

    Hi Ali,

    Yes this worked!

    I made a minor change as I've added several rows to the data source file but I really liked the ROWS(A$1:A4). I changed it to ROWS(A$1:A10 to reflect the increase in rows
    Reply With QuoteRegister To Reply
  15. 07-16-2017, 08:07 AM #15
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,558

    Re: Urgent help with formula please! Stronger index match

    You're welcome! Thanks for letting us know.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. index/match looking up nth row ***URGENT***
    By donnieboy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-09-2015, 05:25 PM
  2. Help with one index/Match formula, urgent!
    By Ceciliecc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-14-2015, 01:27 PM
  3. [URGENT] Require a mutation of "INDEX-MATCH" functions
    By Abid123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2014, 01:10 AM
  4. URGENT SUMPRODUCT INDEX Formula
    By Keelin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-10-2014, 06:46 AM
  5. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  6. Index/Match/Match. Formula looking veritcal and horizontal headings to find a match
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  7. Stronger highlighting of rows for xl97 ?
    By Sirritys in forum Excel General
    Replies: 4
    Last Post: 08-02-2006, 09:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1