INDEX, MATCH-Excel

sudhainfo

New Member
Joined
Aug 15, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Using the Excel formula, I am trying to find values and match values with previous occurrences. The total repetition of values is fetched from another table using vlookup function. Along with this another condition is added to check if the time difference in match value is within 1 hour limit. If it is less than one hour, the value can't be repeated but if it is greater than 1 hour. the value can be repeated.

I used the formula
=IFERROR( INDEX(D4:F4, MATCH(1, (COUNTIF(G$1:$G3, D4:F4) <= VLOOKUP(D4:F4, J$2:K$7, 2, FALSE)) * (LEN(D4:F4) > 0) * ( (COUNTIFS(G$1:$G3, D4:F4) = COUNTIFS($G$1:$G3, D4:F4)) * (IFERROR(MATCH(D4:F4, G$1:G3, 0), 0) >= 0) * (IFERROR(MATCH(1, (ABS(A4 - INDEX(A$1:A3, MATCH(D4:F4, G$1:$G3, 0))) > TIME(1, 0, 0)), 0), 0) >=0) ), 0) ), "")

In this, row 3 should have value of either 328 or d5e because 13a is found in g1 and also time difference between a3 and a1 is less than 1 hour.

Any help to fix this formula is greatly apprecialted.
 

Attachments

  • 2023-08-15_14-08-38.png
    2023-08-15_14-08-38.png
    20.4 KB · Views: 14

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Here's my best guess at answering your question, assuming your file has no Column headers and the desired result is entered in Column M.
Please see my formula for Cell M2, which can be copied to produce the following result. Let me know if you have any additional feedback!
Excel Formula:
=IF($A2-$A$1>=1,$D2,IF(COUNTIFS($G$1:$G2,$D2)=1,$D2,OFFSET($D2,0,1)))
1692303032360.png
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top