+ Reply to Thread
Results 1 to 11 of 11

Offset, Match adjust previous formula

  1. #1
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Offset, Match adjust previous formula

    Hello Excel Forums,

    I need to compare a value from one table to another table to determine a final value, but the formula needs to identify which range of numbers the first values belongs to.

    I had a great formula provided for me for a problem with an extra variable but I'm unable to shorten it without making a mess of it, you can see the original I was provided here.

    =INDEX(Result,MATCH(MIN($F2,4),Data_1,0)+MATCH($G2,OFFSET($L$2,MATCH(MIN(F2,4),Data_1,0),0,COUNTIF(Data_1,MIN($F2,4))),1)-1)

    I've highlighted the parts of the formula that are relevant to what I still need. I no longer need the part that takes column F into account.

    Value to be looked up is on tab 1 column F

    Then the values to be used in the sorting are on tab 4, column D.

    The result to be returned is found on tab 4, column E

    The formula itself needs to be on tab 5, column B

    My explanation is probably terrible sorry, hopefully a look at the workbook will make it clear what I'm trying to do.
    Attached Files Attached Files
    Last edited by quaney; 02-14-2017 at 01:02 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: Offset, Match adjust previous formula

    No workbook attached.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    Hi John,

    Working on it, I had a large data set pasted in a lots of different formula everywhere, workbook was 21mb so been trying to downsize it. Will upload when fully reduced.

  4. #4
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    File now attached.

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,007

    Re: Offset, Match adjust previous formula

    whilst a workbook is attached, there are few formulas in it or reference to cells the formula appeared in
    Last edited by davsth; 02-13-2017 at 11:13 AM.

  6. #6
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    This is a new workbook, the formula I posted was from a previous workbook I had help putting together. I had to remove the vast majority of my work so far to bring the workbook down to the 1MB limit. I left some notes on tab 4 and all columns containing the data to be utilised are highlighted.

    The formula I gave above was what was provided last time but as this is a new sheet with different parameters the cell references will change and so will the structure as it will be using one less variable.

    I can try to re write my problem if it's not clear.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: Offset, Match adjust previous formula

    I admit I have no idea how you arrive at the answer of 11 for Sample 1:

    You are comparing 1875 against column D and somehow arrive at 11

  8. #8
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    Haha I just wrote a random selection of numbers, I was just illustrating that the numbers were to go there, sorry should of made the effort to put the correct ones in to avoid confusion.

  9. #9
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    I've changed the numbers to reflect what the formula should return. Sorry

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,269

    Re: Offset, Match adjust previous formula

    If column A in "Tab 1" and "Tab 5" are in the same sequence then

    in D1

    =VLOOKUP('Tab 1'!$F2,'Tab 4'!$D$3:$E$20,2,1)

    If sequence is different ..

    =VLOOKUP(VLOOKUP($A1,'Tab 1'!$A$2:$G$1000,6,0),'Tab 4'!$D$3:$E$20,2,1)

    OR better

    add column to "Tab 1" to have the result the ..

    in H1

    =VLOOKUP($F2,'Tab 4'!$D$3:$E$20,2,1)

    NOTE: the DIB values must be in ascending order
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-26-2017
    Location
    England
    MS-Off Ver
    MS 10
    Posts
    18

    Re: Offset, Match adjust previous formula

    I'll admit I didn't know using the TRUE method in VLookup would be able to do this, this does exactly what I need (or seems to from the quick test I've done.)

    Thanks very much!

+ 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: 2
    Last Post: 01-25-2017, 01:11 PM
  2. [SOLVED] index match formula won't fill across columns or adjust if column is deleted
    By duvius in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2015, 11:57 AM
  3. Replies: 1
    Last Post: 10-06-2014, 02:13 PM
  4. Formula to adjust a weekend date to the previous Friday...
    By joencrystal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2013, 05:25 PM
  5. Replies: 3
    Last Post: 03-24-2011, 02:00 AM
  6. Adjust OFFSET range
    By BRISBANEBOB in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-23-2009, 05:03 PM
  7. Replies: 7
    Last Post: 11-04-2008, 06:41 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