Forum Discussion

ColeF2070's avatar
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's avatar
    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.

m_tarler's avatar
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")
ColeF2070's avatar
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's avatar
    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.

Resources