+ Reply to Thread
Results 1 to 4 of 4

Help with INDEX / MATCH funtion - return value based on

  1. 04-24-2017, 09:14 AM #1
    filipealves1973
    filipealves1973 is offline
    Registered User
    Join Date
    04-24-2017
    Location
    Lisbon
    MS-Off Ver
    2013
    Posts
    3

    Help with INDEX / MATCH funtion - return value based on

    Hello all

    i have a Excel sheet like this:
    Velocity Weitht Cost
    2ms 216.98 76,111.69 €
    2ms 119.28 69,192.44 €
    2ms 54.89 62,902.22 €
    2ms 31.56 13,155.18 €
    2ms 15.58 11,959.25 €
    2ms 7.78 10,872.05 €
    10ms 29.20 9,883.68 €
    10ms 19.47 11,971.42 €
    10ms 14.60 10,883.11 €
    10ms 9.73 9,893.74 €
    30ms 64.12 8,994.31 €
    30ms 54.22 8,176.64 €
    30ms 38.52 7,433.31 €
    30ms 25.65 6,689.98 €
    30ms 14.75 6,020.98 €

    And i need to:
    - Grab the nearest higher weight, based on velocity (it is fixed in 3 - 2ms / 10 / 30) and weight - being that the variable (it will not be identical to the weights presented).

    Thanks in advance and best regards

    FilipeAlves
    Reply With QuoteRegister To Reply
  2. 04-24-2017, 09:43 AM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Help with INDEX / MATCH funtion - return value based on

    With data in A2:C16,
    Assuming the velocity is set at either 2,10 or 30, I put velocity in E3 and weight in F3.

    The formula for cost is then
    =IFERROR(INDEX($C$2:$C$16, MATCH(F3,OFFSET($A$1, MATCH(E3,$A$2:$A$16,0),1,COUNTIF($A$2:$A$16,E3),1),-1)+MATCH(E3,$A$2:$A$16,0)-1), "Out of Range")
    See attachment.
    Any questions?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 04-24-2017, 09:51 AM #3
    p24leclerc
    p24leclerc is offline
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Help with INDEX / MATCH funtion - return value based on

    you can also try this formula assuming data is in cells A1:C15
    and velocity in E1 and weight in F1
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.
    Reply With QuoteRegister To Reply
  4. 04-24-2017, 10:33 AM #4
    filipealves1973
    filipealves1973 is offline
    Registered User
    Join Date
    04-24-2017
    Location
    Lisbon
    MS-Off Ver
    2013
    Posts
    3

    Re: Help with INDEX / MATCH funtion - return value based on

    Hello ChemistB and p24leclerc

    You guys are amazing!!!
    It worked perfectlly. Many thanks to you! You saved me some hours!

    Thanks a lot!
    Cheers from Portugal!
    FilipeAlves
    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] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  2. [SOLVED] Use Index/Match to return a value based on two criteria? Not working for me...
    By ralldredge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2015, 02:42 PM
  3. Using index and match to return a value based on earliest occurrence (MONTH)
    By potatoman in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-09-2014, 06:12 AM
  4. Using index and match to return a value based on earliest occurrence (MONTH)
    By potatoman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2014, 06:16 AM
  5. [SOLVED] Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion
    By clemsoncooz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2014, 05:16 PM
  6. [SOLVED] Using Index, Match to return the next event based on Today()
    By JO505 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2013, 07:51 PM
  7. [SOLVED] Excel 2007 : Using INDEX and MATCH (or other) to return cell based on largest associated value
    By dip11 in forum Excel General
    Replies: 30
    Last Post: 12-19-2011, 12:13 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1