+ Reply to Thread
Results 1 to 4 of 4

Index Match to bring a value between a range

  1. #1
    Registered User
    Join Date
    04-18-2017
    Location
    Barcelona
    MS-Off Ver
    Office 2016
    Posts
    2

    Index Match to bring a value between a range

    Hello!
    I have tried to work out the formula and find the answer to this in the forum, it's probably been resolved in a different threat but I can't find it so I hope someone can help me with the answer or the link of the treat where this has been solved.
    I have a table where I have a code in the horizontal column and quantity in the vertical and I need the index match to bring the value also when the quantity is between a range.
    I.e, I have code ES0697 with quantity 35K, this price is between the 30&50K so the formula should bring the price for 30K. If the quantity is 53K should bring zero as there is no price in that range.

    Thanks!!
    Attached Files Attached Files
    Last edited by kricek7; 06-12-2017 at 08:51 AM.

  2. #2
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Index Match to bring a value between a range

    Pretty sure there is an easier way to do this but this is the long version.

    =VLOOKUP(F17,A2:I13,MATCH(IF(F18<=5000,5000,IF(F18<=10000,10000,IF(F18<=20000,20000,IF(F18<=30000,30000,IF(F18<=50000,50000,IF(F18<=100000,100000,IF(F18<=250000,250000,IF(F18<=500000,500000,"")))))))),A2:I2,0),FALSE)

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index Match to bring a value between a range

    So close.....

    =INDEX(B3:I13,MATCH(F17,A3:A13,0),MATCH(F18,B2:I2,1))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    04-18-2017
    Location
    Barcelona
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Index Match to bring a value between a range

    I can't believe it was that easy... thanks both!!

+ 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: 4
    Last Post: 04-28-2017, 03:24 AM
  2. Index Match to find match date to a range of dates
    By downcrusher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:05 PM
  3. Index/Match - Dynamic Index Range with Hlookup
    By WassimJMP in forum Excel General
    Replies: 3
    Last Post: 02-16-2016, 12:24 PM
  4. Replies: 5
    Last Post: 02-09-2016, 07:02 PM
  5. Using Match/Index to bring multiple values
    By javi07 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-27-2014, 03:02 AM
  6. [SOLVED] Index Match Match for weekly range
    By onemoremile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2014, 04:46 PM
  7. index and match in multiple worksheets and bring corresponding row
    By nickgoldie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2010, 04:32 PM

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