UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Offset/match ?, Any Version    
 
   
tpitman
post Mar 17 2020, 03:54 AM
Post#1



Posts: 664
Joined: 20-June 04
From: UK


I have 1000's of records recording wind speed.

I can use Max(J:J) to find the Max wind this year, but I would like to find the corresponding Day/Month/Year. I assume I would use Offset/ Match but unsure how to use it for this ( eg attached)



Attached File(s)
Attached File  Clipboard01.jpg ( 35.61K )Number of downloads: 2
 
Go to the top of the page
 
Vince
post Mar 17 2020, 04:02 AM
Post#2



Posts: 78
Joined: 18-August 16
From: Bristol, UK


You can use:

MATCH(MAX(J:J),J:J,0)

to find the row number and then work with that, BUT I would guess that the maximum windspeed (& gustspeed) is unlikely to be a unique value so could occur on more than one date?
Go to the top of the page
 
Vince
post Mar 17 2020, 04:39 AM
Post#3



Posts: 78
Joined: 18-August 16
From: Bristol, UK


Better still, have a look here:

https://support.office.com/en-gb/article/xl...f9-88eae8bf5929

Found a new function; XLOOKUP.
Go to the top of the page
 
tpitman
post Mar 17 2020, 04:40 AM
Post#4



Posts: 664
Joined: 20-June 04
From: UK


hanks for this.

Yes it will, but I'm happy to pick out the first occurrence.

So how do I add Offset to find the value for Columns A, B & C ( Day, Month, Year)
Go to the top of the page
 
tpitman
post Mar 17 2020, 04:54 AM
Post#5



Posts: 664
Joined: 20-June 04
From: UK


Works a treat!!

Thank You!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 04:42 AM