Return Value to the Left of Minimum Value

J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,557
Office Version
  1. 365
Platform
  1. Windows
I am tracking work order production so I have dates in column A and values in column B. I am trying to search for the minimum value in column B and return the date directly to the left of that minimum value. I searched and tried INDEX MATCH and OFFSET but they are not working.

Any ideas?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
What does your data look like? Are the date sorted? Please post a sample.
 
Upvote 0
can you post some sample data using the xl2bb add in? Also, are there duplicates in your list of values you are scanning through?
 
Upvote 0
Justinian said:
I tried xl2bb but it says not supported in protected view.
Click to expand...
Then post the data as a table. Highlight a1:B25. Copy, come to start a reply post
here. click in the posting area, click paste or CNTL-V.
 
Upvote 0
Got it:

Book1
AB
11/2/20241020
21/3/20241079
31/4/20241106
41/5/20241097
51/8/20241250
61/9/20241304
71/10/20241316
81/11/20241352
91/12/20241335
101/15/20241372
111/16/20241366
121/17/20241403
131/18/20241381
141/19/20241335
151/22/20241410
161/23/20241419
171/24/20241374
181/25/20241331
191/26/20241296
201/29/20241332
211/30/20241359
221/31/20241334
232/1/20241330
242/2/20241323
252/5/20241530
262/6/20241620
272/7/20241646
282/8/20241661
292/9/20241527
302/12/20241536
312/13/20241523
322/14/20241414
332/15/20241337
342/16/20241253
352/19/20241280
362/20/20241190
372/21/20241169
382/22/20241190
392/23/20241106
402/26/20241099
412/27/20241085
422/28/20241048
432/29/2024987
443/1/2024955
453/4/2024982
463/5/2024980
473/6/2024957
483/7/2024920
493/8/2024836
503/11/2024876
513/12/2024822
523/13/2024825
533/14/2024811
543/15/2024743
553/18/2024745
563/19/2024743
573/20/2024686
583/21/2024631
593/22/2024542
603/25/2024578
613/26/2024500
623/27/2024504
633/28/2024473
643/29/2024485
654/1/2024490
664/2/2024479
674/3/2024477
684/4/2024438
694/5/2024402
704/8/2024441
714/9/2024421
724/10/2024399
734/11/2024391
744/12/2024377
754/15/2024371
764/16/2024350
774/17/2024297
Sheet1
 
Upvote 0
have a try:
工作簿1
ABCDE
1datavaluedate
22024-4-18162024-4-24
32024-4-1972
42024-4-2097
52024-4-214
62024-4-229
72024-4-2353
82024-4-241
92024-4-2519
102024-4-2684
112024-4-2744
122024-4-2830
132024-4-2976
14
Sheet1
Cell Formulas
RangeFormula
D2D2=TOCOL(A2:A13/(B2:B13=MIN(B2:B13)),3)
B2:B13B2=RANDBETWEEN(1,99)
 
Upvote 0
Solution
When you have multiple instances of the same min, which date do you want to return?
 
Upvote 0
Cubist said:
When you have multiple instances of the same min, which date do you want to return?
Click to expand...
So far, there have not been duplicates, in which case the most recent date will be the one I want.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Locked
  • Question Question
Getting the heading name of minimum value followed by reference numbers
Replies
1
Views
182
Fluff
Fluff
C
  • Question Question
VBA - Creating a Dashboard with Charts including data changes
Replies
1
Views
264
Jon Peltier
J
F
  • Question Question
Nested IF functions
Replies
5
Views
348
shift-del
shift-del
V
  • Question Question
Find minimum rows from column A that would cover all columns
Replies
3
Views
127
Vlada
V
R
  • Question Question
Lowest value in range of cells excluding this row
2
Replies
10
Views
960
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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