+ Reply to Thread
Results 1 to 16 of 16

INDEX MATCH with row offest

  1. #1
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Smile INDEX MATCH with row offest

    I have a sheet with “Sl. No.” and “Round Off” in columns from B through E [DATA]

    In columns G & H [OUTPUT] “Sl. No.” is entered, the “Round Off” from the DATA columns need to be reflected in the “Final Rate” field in OUTPUT.

    When the row of “Sl. No.” and “Round off” are same in DATA field, the following formula in H3 works perfectly.
    =INDEX($E$3:$E$12,MATCH(G3,$B$3:$B$12,0))

    But, when the “Sl. No.” and “Round off rates” are in different rows, it does not work.

    How to modify in order that the formula works as per the desired output? The work file is attached herewith.

    Cs0bGe8.jpg
    Attached Files Attached Files
    Last edited by melvinkoshy; 01-06-2019 at 02:39 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: INDEX MATCH with row offest

    This can be achieved with a user defined function.

    Please Login or Register  to view this content.
    Open the VBA editor by hitting Alt F11.
    Insert a new module with Insert - Module
    Paste in the above function
    Go back to the sheet by hitting Alt F11.

    In H2, enter =GetFinalRate(G3,B:B,E:E) and copy down


    Remember to save the workbook as a macro enabled workbook .xlsm
    Martin

  3. #3
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Thanks , This code works like charm. I tried the across different sheets by changing the arguments inside the function and works as desired
    Last edited by melvinkoshy; 01-06-2019 at 08:22 PM.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    Or formula

    G3 copy to H3 and copy down
    =IFERROR(INDEX($B:$E,AGGREGATE(15,6,ROW($B$3:$B$20)/($B$3:$B$20<>""),ROWS(G$3:G3)),COLUMNS($G3:G3)*3-2),"")

  5. #5
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Quote Originally Posted by Bo_Ry View Post
    Or formula

    G3 copy to H3 and copy down
    =IFERROR(INDEX...
    Could you please re-check this code? I copied this code to H3 and the "Final Rate" against "Sl. No" 1.00 is returned as 1.00

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    Please see attached,
    Attached Files Attached Files

  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: INDEX MATCH with row offest

    Here's the user defined function version

    Please Login or Register  to view this content.
    =GetRoundupSum(B3,B:B,D:D) in E3 and copy down

  8. #8
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Quote Originally Posted by mrice View Post
    Here
    =GetRoundupSum(B3,B:B,D:D) in E3 and copy down
    Thanks for the UDF code for getting the sum. I applied the formula and the results are as shown. The sum of Sl. No. 3.0 and 4.0 are wrong as it is considering only the last value.
    Attached Images Attached Images
    Last edited by melvinkoshy; 01-07-2019 at 09:12 PM.

  9. #9
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Quote Originally Posted by Bo_Ry View Post
    Please see attached,
    I opened the file to see this in output. What is amiss here? The "Final rate" against 3.0 and 4.0 are shown as zero.
    Attached Images Attached Images

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    I just follow your Post#1 image,

    If you want 211 and 141 for 3 and 4 Then H3 copy down

    =INDEX($E$3:$E$12,-LOOKUP(1,-(MATCH(G3:G4,$B$3:$B$12)-{0;1})))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Quote Originally Posted by Bo_Ry View Post
    I just follow your Post#1 image,
    If you want 211 and 141 for 3 and 4 Then H3 copy down
    =INDEX($E$3:$E$12,-LOOKUP(1,-(MATCH(G3:G4,$B$3:$B$12)-{0;1})))
    The above formula works when Sl. No. field in output is continuous. If there are some blank rows in Sl. No., the 'Final Rate' returns "0.00" as shown
    Attached Images Attached Images

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    Try
    =IFERROR(INDEX($E$3:$E$12,MAX(MATCH(G3+1,$B$3:$B$12)-1,MATCH(G3,$B$3:$B$12))),"")

    Untitled.png

    Why do you manually add blank rows when formula from Post#2 already gives the result?


    Instead of keep changing this and that, Please provide the sheet with the final expected result.

  13. #13
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    Quote Originally Posted by Bo_Ry View Post
    Why do you manually add blank rows when formula from Post#2 already gives the result? Instead of keep changing this and that, Please provide the sheet with the final expected result.
    My requirement is that I have an ESTIMATE sheet and RATE sheet

    The Final Rate field (obtained by rounding off Amount values/ sum of few amount values as the case may be) from the RATE sheet are required to be matched to the ESTIMATE sheet by referring to the Sl. No field. Initially, my requirement was to get final rate corresponding to the Sl. No from the DATA sheet which was served by following user-defined function entered in Cell I5 of Rate field of ESTIMATE sheet.

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

    Later, I wished to get the Final Rate field in RATE sheet also automated for which I used the following in Cell K2 of Final Rate in RATE Sheet

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

    However, I observed that the values of Final rate in RATE sheet which are obtained by summing values in Amount Field of RATE sheet are not matched in ESTIMATE Sheet. Hence, in the ESTIMATE Sheet, Item 2.1, 3.0 and 4.0 are blank and not reflecting the Final Rate (in RATE Sheet); 44,081.00 , 2,408.00 , 513.00 respectively.

    I also thought user defined function would be of help. Hence, I tried GetRoundupSum in Final Rate field of RATE Sheet, which was not returning the correct sum.

    I apologise for posting the problem many times as I was trying to evolve the different cases for my requirement. The worksheet is attached herewith
    Attached Files Attached Files
    Last edited by melvinkoshy; 01-08-2019 at 12:19 PM.

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    Is this right?

    Estimate I4 Press Ctrl+Shift+Enter

    =IFERROR(INDEX(ESTIM5[Final Rate],MAX(IFERROR(MATCH(1,--(ESTIM5[Est Sl. No]>E5)*(ESTIM5[Est Sl. No]<>""),)-1,),MATCH(E5,ESTIM5[Est Sl. No],))),"")

    If not, Please provide manually mockup answer in the sheet, instead of formula that gives the wrong answer.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    78

    Re: INDEX MATCH with row offest

    @Bo_Ry The formula works as desired. As it is an array formula, would it slow the calculation if the row data in RATE Sheet exceeds say 600 rows.

    Also, why did my formula in Cell K2 of Final Rate in RATE Sheet not work? I was thinking if the user-defined function code could not recognise a cell with no value in Final rate.?

  16. #16
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: INDEX MATCH with row offest

    1. No problem for 600 rows, maybe slow for more than 10k rows.

    2. Maybe, I didn't check that.

+ 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. [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] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Index,Offest, Match, Wildcard?
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-18-2014, 11:27 PM
  5. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. value in offest cell to match
    By short_n_curly in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2010, 04:02 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1