Identify and store a custom parameter into a LET function

Hi everyone,

I have two main columns in my file that I want to use:

"F ID" - a column with IDs;
"S DP" - a column with either numerical values or "Not correct value" as text;

I have a function that does other things, which are un-related to my current question, on column "H" (progress until now). What I want to do is add to this function a range that finds the first occurrence of a numerical value on column "S DP", for each F ID, and stores it in the LET function for later use.

My current LET function:

=LET(NOT_CORRECT,"Not correct value",

count_ROWS,ROWS('DP & PAC'!$B$2:$B$7424)+2,

table_RANGE_YEAR,C2:INDEX(C:C,count_ROWS),
table_RANGE_SUBMITTED_DP,D2:INDEX(D:D,count_ROWS),
table_RANGE_INVESTMENT,F2:INDEX(F:F,count_ROWS),
table_RANGE_FUNDRAISING,E2:INDEX(E:E,count_ROWS),
table_RANGE_FundID,B2:INDEX(B:B,count_ROWS),


MIN_YEAR,MIN(table_RANGE_YEAR),
MAX_YEAR,MAX(table_RANGE_YEAR),

table_RANGE_OFFSET_INVESTMENT,OFFSET(table_RANGE_INVESTMENT,1,0),
table_RANGE_OFFSET_FUNDRAISING,OFFSET(table_RANGE_FUNDRAISING,1,0),


table_RANGE_INV_AMT_INV_PER_YEAR, IF( table_RANGE_YEAR = MIN_YEAR, table_RANGE_INVESTMENT, (table_RANGE_INVESTMENT - table_RANGE_OFFSET_INVESTMENT)),
table_RANGE_FR_AMT_RAISED_PER_YEAR, IF( table_RANGE_YEAR = MIN_YEAR, table_RANGE_FUNDRAISING, (table_RANGE_FUNDRAISING - table_RANGE_OFFSET_FUNDRAISING)),



table_RANGE_FR_AMT_RAISED_PER_YEAR)



---------------------

Does someone know a solution for this? i've added a few end-results manually on the "manual input" column, with the desired outcome of the function.

Many thanks!!
S

Very interesting solution, thank you for this! I like it.

Is there a way for which "S_DP" returns two columns, one being the F ID and the other being the first amount on column D (as in your current formula result)?

I used the filter function you provided & coupled it with a VLOOKUP in order to end up with one value per F ID - theoretically the first value it finds, but for some reason it brings up exactly the first value it finds from the bottom - instead of the first value from the top.

Do you know why this is?

Here is my result based on your filter formula, with VLOOKUP that brings almost what I want to bring, however it brings the first result from the bottom instead of from the top:

Please Login or Register  to view this content.

Also used this from what you suggested, but as a last step I;m trying to get "S_DP" to show F ID as well instead of just the values:

Please Login or Register  to view this content.

Perfect, thank you very much, I must have missed it due to tiredness!

I'll mark it as solution and close the thread.