# INDEX MATCH with row offest

1. ## 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

2. ## 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

3. ## 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

4. ## 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. ## Re: INDEX MATCH with row offest

Originally Posted by Bo_Ry
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

7. ## 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. ## Re: INDEX MATCH with row offest

Originally Posted by mrice
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.

9. ## Re: INDEX MATCH with row offest

Originally Posted by Bo_Ry
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.

10. ## Re: INDEX MATCH with row offest

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})))

11. ## Re: INDEX MATCH with row offest

Originally Posted by Bo_Ry
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

12. ## 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. ## Re: INDEX MATCH with row offest

Originally Posted by Bo_Ry
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:
`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:
`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

14. ## 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],))),"")

15. ## 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.?

Page 1 of 2 1 2 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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