Identify and store a custom parameter into a LET function

**Bloodraven** · 09-26-2022, 10:39 AM

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

**TMS** · 09-26-2022, 12:53 PM

Maybe this:

Formula:



=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)),



 f,FILTER(A2:D7424,D2:D7424<>NOT_CORRECT),uc,UNIQUE(INDEX(f,0,2)),S_DP,INDEX(f,MATCH(uc,INDEX(f,,2),FALSE),4),



 table_RANGE_FR_AMT_RAISED_PER_YEAR)

**TMS** · 09-26-2022, 12:54 PM

This lists the IDs and most recent values:

Formula:

=LET(f,FILTER(A2:D7424,D2:D7424<>"Not correct value"),uc,UNIQUE(INDEX(f,0,2)),rv,INDEX(f,MATCH(uc,INDEX(f,,2),FALSE),4),s_dp,CHOOSE({1,2},uc,rv),s_dp)

**TMS** · 09-26-2022, 01:00 PM

Or this, using your variables:

Formula:



=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)),



 f,FILTER(B2:INDEX(D:D, count_ROWS),table_RANGE_SUBMITTED_DP<>NOT_CORRECT),uc,UNIQUE(INDEX(f,0,1)),S_DP,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),



 table_RANGE_FR_AMT_RAISED_PER_YEAR)

**TMS** · 09-26-2022, 06:55 PM

I was bemused that my formula looked right but, instead of returning zero for the first ID, it returned a value. I investigated and realised that I had missed a parameter (FALSE) from the MATCH function.

some corrected examples

List IDs and values

Formula:

=LET(f,FILTER(B2:D7424,D2:D7424<>"Not correct value"),uc,UNIQUE(INDEX(f,0,1)),rv,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),TMS_DP,CHOOSE({1,2},uc,rv),TMS_DP)

List values

Formula:

=LET(f,FILTER(B2:D7424,D2:D7424<>"Not correct value"),uc,UNIQUE(INDEX(f,0,1)),S_DP,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),S_DP)

List values using OP's variables

Formula:

=LET(NOT_CORRECT,"Not correct value",

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

table_RANGE_SUBMITTED_DP,D2:INDEX(D:D,count_ROWS),

f,FILTER(B2:INDEX(D:D,count_ROWS), table_RANGE_SUBMITTED_DP <> NOT_CORRECT),   uc,UNIQUE(INDEX(f,0,1)),   S_DP,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),

S_DP)

Column H Formula:

Formula:

=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)),



 f,FILTER(B2:INDEX(D:D, count_ROWS),table_RANGE_SUBMITTED_DP<>NOT_CORRECT),uc,UNIQUE(INDEX(f,0,1)),S_DP,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),



 table_RANGE_FR_AMT_RAISED_PER_YEAR)

**TMS** · 09-26-2022, 07:02 PM

Is this resolved to your satisfaction?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**Bloodraven** · 09-27-2022, 04:11 AM

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:

=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)),



f,FILTER(B2:INDEX(D:D, count_ROWS),table_RANGE_SUBMITTED_DP<>NOT_CORRECT),
v,VLOOKUP(table_RANGE_FundID,f,3,TRUE),
v)

**Bloodraven** · 09-27-2022, 04:12 AM

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:

=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)),



f,FILTER(B2:INDEX(D:D, count_ROWS),table_RANGE_SUBMITTED_DP<>NOT_CORRECT),
uc,UNIQUE(INDEX(f,0,1)),
S_DP,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),



S_DP)

**TMS** · 09-27-2022, 04:24 AM

The first formula in post #5 does what you want.

Formula:

=LET(f,FILTER(B2:D7424,D2:D7424<>"Not correct value"),uc,UNIQUE(INDEX(f,0,1)),rv,INDEX(f,MATCH(uc,INDEX(f,,1),FALSE),3),S_DP,CHOOSE({1,2},uc,rv),S_DP)

**Bloodraven** · 09-27-2022, 04:26 AM

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

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

**TMS** · 09-27-2022, 04:33 AM

You're welcome. Thanks for the rep.

Identify and store a custom parameter into a LET function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Re: Identify and store a custom parameter into a LET function

Thread Information

Users Browsing this Thread

Similar Threads

Power query Custom function / parameter query

[SOLVED] Power Query Custom Column to identify First Occurrence of Data

[SOLVED] Identify the last sale orders for each store

Looking for a way to identify and store different strings

Identify a custom menu control

[SOLVED] How to store custom data into Excel cell

[SOLVED] Format when sending a range as parameter to a custom function

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions