+ Reply to Thread
Results 1 to 6 of 6

INDEX MATCH with Dynamic Range

  1. 09-16-2024, 12:28 PM #1
    Stuepef
    Stuepef is offline
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2019
    Posts
    77

    INDEX MATCH with Dynamic Range

    I have a spreadsheet that shows the cost difference for fruit between multiple carriers. The ranges in the spreadsheet can be dynamic based on how many fruits or carriers are being compared. I am looking for a way to obtain the monthly cost total by carrier knowing the number of Options can be dynamic. The words Current and Option will always be constant in the export making that a starting point to identify the ranges. I am trying to avoid using INDIRECT if possible.

    In row 1, the range should start at the word "Current" (B1)+2 and find the word "*Option*" (H1)-2 to form the range of B3:F31.

    Then, sum the monthly cost per fruit for the "Current" option of $100 + $200 + $300 = $600 for the current option.

    Here is the start of my formula:
    Please Login or Register  to view this content.
    Thank you!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-16-2024, 01:23 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: INDEX MATCH with Dynamic Range

    With Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 09-16-2024, 04:36 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    47,995

    Re: INDEX MATCH with Dynamic Range

    Try:

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


    And:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 09-16-2024, 09:58 PM #4
    Stuepef
    Stuepef is offline
    Registered User
    Join Date
    10-25-2017
    Location
    United States
    MS-Off Ver
    2019
    Posts
    77

    Re: INDEX MATCH with Dynamic Range

    Both options work - thank you!
    Reply With QuoteRegister To Reply
  5. 09-16-2024, 10:23 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    47,995

    Re: INDEX MATCH with Dynamic Range

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  6. 09-16-2024, 10:48 PM #6
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,669

    Re: INDEX MATCH with Dynamic Range

    thanks for the rep. You may want to look at getting the Power Query book by Ken Puls and Miguel Escobar on Amazon./
    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. [SOLVED] Dynamic Range with Index/Match Help
    By brianbohannan91 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2021, 03:52 AM
  2. Dynamic Range and Index Match
    By carrollm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2019, 10:11 AM
  3. Index & Match (Dynamic Range)
    By stevoDE in forum Excel General
    Replies: 1
    Last Post: 11-15-2017, 08:11 AM
  4. INDEX(MATCH) dynamic column range?
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2016, 03:46 PM
  5. Index/Match - Dynamic Index Range with Hlookup
    By WassimJMP in forum Excel General
    Replies: 3
    Last Post: 02-16-2016, 12:24 PM
  6. Use Dynamic Range for index match?
    By bvokey in forum Excel General
    Replies: 3
    Last Post: 02-14-2016, 09:04 PM
  7. Vba index and match for dynamic range
    By _google in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-20-2013, 10:25 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1