Search code examples
excelexcel-formulavlookup

Function to search for specific number and then to further search for the prefix

I have a huge amount of data to process in which 4 points with a related prefix needs to be subtracted from each other. Data consists of ID and x value

Example ID = 290.12, 290.03, 290.06, 290.09, 300.12, 300.03, 300.06, 300.09, 301.12, 301.03, 301.06, 301.09 (let's call prefix a "ring number" and suffix time on the clock) X value = any numerical value for each ID assigned

What I'm hoping to do is to search for the first number before the dot i.e. 300 and then subtract the value of 300.06-300.12 in one cell and in another cell 300.03-300.09.

(The subtraction is just an example, how I need to manipulate with the numbers is slightly more complicated, but I got this one under control)

This is my actual Data and what I need to produce is to the right of the raw data. At the moment, I'm doing it manually for each set of "rings"

Anyone knows how to approach this? I'm thinking vlookup, but I'm not very proficient in excel.

New Excel

I tried vlookup, but I don't know how to construct the formula and I run out of ideas.

Solution

  • Edit:

    I found out that REDUCE is no requirement in this case, so it can be shortened to:

    =SQRT(SUM(((INDEX(B:D,XMATCH(I3+0.09,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(I3+0.03,A:A),SEQUENCE(1,3)))^2)))

    You could change +0.09 and +0.03 to your needs and may reference them using LET() for easy maintaining:

    =LET(id,I3, 
     _id1,0.09,
     _id2,0.03,
SQRT(SUM(((INDEX(B:D,XMATCH(id+_id1,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id2,A:A),SEQUENCE(1,3)))^2))))

    Previous answer:

    =LET(
 id,I3,
_id1,0.09,
_id2,0.03,
SQRT(
     REDUCE(0, SEQUENCE(1,3),
     LAMBDA(x, y,
     x+((INDEX(B:D,XMATCH(id+_id1,A:A),y)
     -INDEX(B:D,XMATCH(id+_id2,A:A),y))
     ^2)))))

    This formula looks for the matching value of the id value I3 + _id1 minus the matching value of id value + _id2 for columns B to D and adds the ^2 results per column. Then it calculates it's square root.

    You can change _id1 and _id2 to your needs.

    enter image description here

    To calculate the Delta (as shown) at once you could use:

    =LET(id,I3,
     _id1,0.09,
     _id2,0.03,
     _id3,0.12,
     _id4,0.06,
     x,SQRT(SUM((INDEX(B:D,XMATCH(id+_id1,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id2,A:A),SEQUENCE(1,3)))^2)),
     y,SQRT(SUM((INDEX(B:D,XMATCH(id+_id3,A:A),SEQUENCE(1,3))-INDEX(B:D,XMATCH(id+_id4,A:A),SEQUENCE(1,3)))^2)),
(x-y)*1000)

    You can have a column of unique values of the integers and a new column where you reference these values as id and drag down the formula to get your row by row result

    In another column you can refer to these columns and sort per the second column using SORTBY()