+ Reply to Thread
Results 1 to 6 of 6

Seatch table for a best matching value and returning the column header

  1. #1
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Seatch table for a best matching value and returning the column header

    I have a forecast file that I did some tweaking so that the quantities are cumulative

    Basically what I want to do is determine by which week a SKU depletes

    I intend to do this via the method shown in my sample data

    For step 1 I can use MATCH

    For step 3 I should be able to derive it using the output from step 2 using the MATCH and COLUMN function

    Only problem is, I cant get the MATCH function to work for step 2 since from what I understand, MATCH only works for exact figures and Hlookup wont work

    What do I do?
    Attached Files Attached Files
    Last edited by lordfa9; 06-07-2019 at 11:34 PM.

  2. #2
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,981

    Re: Bringing together a few seperate ideas

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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.

  3. #3
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Seatch table for a best matching value and returning the column header

    fixed.......

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Seatch table for a best matching value and returning the column header

    Hi

    I suppose you want this.

    Try in E19 and copy down

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

  5. #5
    Forum Contributor
    Join Date
    05-07-2007
    Location
    Singapore
    MS-Off Ver
    2006/2016
    Posts
    170

    Re: Seatch table for a best matching value and returning the column header

    sweet, works like a charm

    Just a closing question: Why the 0.0000001 in MATCH(C19+0.0000001)? Is this the thing that pushes the formula to look one more cell to the right?

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Seatch table for a best matching value and returning the column header

    Hi

    To work like a charm I need do a correction. Not 'C19+0.0000001' but 'C19-0.0000001'

    The explanation is that when a value has an exact match the math function gives that position (correctly) but my formula adds one to that position.

    Try the new formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    without -0.0000001 with the value in P22=4000 and you see the week 35
    With -0.0000001 and P22=4000 you will see the correct solution week 34.

+ 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. Seperate all worksheets from one workbook into seperate files (.xlsm)
    By inzanesrt4 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-08-2016, 05:04 PM
  2. Creating email from seperate data in seperate cells
    By Binary to Books in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2014, 08:20 PM
  3. [SOLVED] Seperate First, Middle and Last name to seperate columns
    By Accusoft in forum Excel General
    Replies: 4
    Last Post: 04-05-2014, 04:51 AM
  4. [SOLVED] Macro to seperate worksheets to seperate workbooks fired from external excel file
    By optionone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2012, 01:25 PM
  5. Seperate Values in single cell seperated by [alt+enter] into seperate cells
    By Coquito in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2012, 04:24 PM
  6. [SOLVED] Seperate Comma seperated values in seperate rows in different tab of same worksheet
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-28-2012, 04:54 AM
  7. [SOLVED] How do I seperate data from a pivot into seperate worksheets?
    By Shannon in forum Excel General
    Replies: 5
    Last Post: 08-25-2005, 02:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1