Forum Discussion

ColeF2070

Copper Contributor

Aug 12, 2024

Solved

Lookup and Iterate

I have a dataset that i would like to use to lookup but I need to iterate between set values such as 1:03 that falls between 1:00 and 1:05 AFTER my time of concentration is greater than 1 hr. Any hel...

excel

Formulas and Functions

m_tarler

Aug 12, 2024

ColeF2070 ah. here is one option (I'll think and prob add another):

=IFERROR(
FORECAST.LINEAR(
   $L$3,
   INDEX('Riley County Rainfall Intensity'!$B$2:$G$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1},ROW(K9)-ROW($K$8)),
   INDEX('Riley County Rainfall Intensity'!$A$2:$A$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1})),
"not found")

and then drag down

and an option using new array formula:

=LET(in, G3,
vals, CHOOSEROWS('Riley County Rainfall Intensity'!A2:G117,XMATCH(G3,'Riley County Rainfall Intensity'!A2:A117,-1)+{0,1}),
diff, (in-INDEX(vals,1,1))/(INDEX(vals,2,1)-INDEX(vals,1,1)),
DROP(TRANSPOSE(diff*(TAKE(vals,-1)-TAKE(vals,1))+TAKE(vals,1)),1)
)

but maybe someone else knows an easier way.

rainfall.xlsx18 KB

m_tarler

Bronze Contributor

Aug 12, 2024

ColeF2070 first I want to thank you for formatting that all in tables to make things easier. next I think what you want is to fill in the set of data from 2 year to 100 year based on that Tc= value.

That said I think you have some typos/error in that the lookup row you have is 1 below the data you listed except for the 50 year which is completely different row.

In the attached I give 2 examples:

using new 365 formula:

=TRANSPOSE(XLOOKUP(G3,'Riley County Rainfall Intensity'!A2:A117,'Riley County Rainfall Intensity'!B2:G117,"not found",1))

using older formula and then hit ctrl-shift-enter (and I think it will work but I can't test):

=IFERROR(TRANSPOSE(INDEX('Riley County Rainfall Intensity'!B2:G117,MATCH(L3,'Riley County Rainfall Intensity'!A2:A117,1),0)),"not found")

oops had to edit to find nearest. Note: the newer XLOOKUP will let you easily select next larger or next lower but the older Match will only find next lower so if you want the next higher you can do a small minus then +1 to the index so something like this:

=IFERROR(TRANSPOSE(INDEX('Riley County Rainfall Intensity'!B2:G117,MATCH(L3-0.001,'Riley County Rainfall Intensity'!A2:A117,1)+1,0)),"not found")

rainfall.xlsx18 KB

ColeF2070
Copper Contributor
Aug 12, 2024
m_tarler Thank you, I may very well have some errors lol, I am trying to get a result between two sets of values when the referenced cell is between the values on the table. (ie Time = 1:03 falls between time 1:00 and 1:05 so something that iterates with the givens. (1:05-1:00/1.51-1.59)=(1:05-1:03/1.51-x))
- m_tarler
  Bronze Contributor
  Aug 12, 2024
  ColeF2070 ah. here is one option (I'll think and prob add another):
  
  =IFERROR( FORECAST.LINEAR( $L$3, INDEX('Riley County Rainfall Intensity'!$B$2:$G$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1},ROW(K9)-ROW($K$8)), INDEX('Riley County Rainfall Intensity'!$A$2:$A$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1})), "not found")
  
  and then drag down
  
  and an option using new array formula:
  =LET(in, G3, vals, CHOOSEROWS('Riley County Rainfall Intensity'!A2:G117,XMATCH(G3,'Riley County Rainfall Intensity'!A2:A117,-1)+{0,1}), diff, (in-INDEX(vals,1,1))/(INDEX(vals,2,1)-INDEX(vals,1,1)), DROP(TRANSPOSE(diff*(TAKE(vals,-1)-TAKE(vals,1))+TAKE(vals,1)),1) )
  but maybe someone else knows an easier way.
  rainfall.xlsx18 KB