Forum Discussion
Pulling cell value based on sequence of numbers.
Hi,
I have created a spreadsheet to map data flow for my company and assign a scoring of 1 - 3 for four categories.
I have a list of a risk ratings based on the 4 scores given.
The scoring looks like this on the main tab. I've added a section at the bottom of the first line to input the risk rating (which I'll replicate when I've figured it out how to do it). I want it to pull the rating from the list based on the 4 scores given.
so for the first entry from G8 the example above the risk rating will be 'Low'. as it would flow 3,3,3,1. which is the third sequence on the left hand list on the snip above.
I assume this would be easier if the risk rating list was in the same format as the scoring table on the main tab but I couldn't work it out when I changed the list.
any help would be appreciated and apologies if my query is not clear.
I'm not sure if I've understood your task correctly. If not - sorry.
With the following formula, you can filter out the risk from your 4 cells 3,3,3,1 from your table.In my example, the three blocks with the risk codes are in A2:E28,G2:K28 and M2:Q28. The 4 cells with data mapping are in S5:T6
=LET(risklist,VSTACK(A2:E28,G2:K28,M2:Q28),rlcode,CHOOSECOLS(risklist,1)&CHOOSECOLS(risklist,2)&CHOOSECOLS(risklist,3)&CHOOSECOLS(risklist,4),XLOOKUP(TEXTJOIN("",FALSE,S5:T6),rlcode,CHOOSECOLS(risklist,5)))I have also written a user-defined lambda function in my example file:
=semaj(A2:E28,G2:K28,M2:Q28,S5:T6)The function does the same as above.
This function combines data from three different ranges into a single list, constructs a code by concatenating the first four columns of each row, and then performs a lookup to find a corresponding value from the fifth column based on a combined string from another range.
In essence, this function merges multiple data sets, creates unique identifiers for each row, and retrieves specific data based on a combined lookup value.
2 Replies
I'm not sure if I've understood your task correctly. If not - sorry.
With the following formula, you can filter out the risk from your 4 cells 3,3,3,1 from your table.In my example, the three blocks with the risk codes are in A2:E28,G2:K28 and M2:Q28. The 4 cells with data mapping are in S5:T6
=LET(risklist,VSTACK(A2:E28,G2:K28,M2:Q28),rlcode,CHOOSECOLS(risklist,1)&CHOOSECOLS(risklist,2)&CHOOSECOLS(risklist,3)&CHOOSECOLS(risklist,4),XLOOKUP(TEXTJOIN("",FALSE,S5:T6),rlcode,CHOOSECOLS(risklist,5)))I have also written a user-defined lambda function in my example file:
=semaj(A2:E28,G2:K28,M2:Q28,S5:T6)The function does the same as above.
This function combines data from three different ranges into a single list, constructs a code by concatenating the first four columns of each row, and then performs a lookup to find a corresponding value from the fifth column based on a combined string from another range.
In essence, this function merges multiple data sets, creates unique identifiers for each row, and retrieves specific data based on a combined lookup value.
