Forum Discussion
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...
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.
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))
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.