# How to use the value of a cell to find another cell?

I’m trying to create my own data sheets for reading at school but when I put the reading ages in, I need it to select the zpd level and the reading targets automatically base on what I input.

Eg if I put 16.06 in reading ages (cell c2) , I want it to say 5-13 (in cell d2) automatically. Then I want it to say 26 in termly target (cell e2)

But if I put 15.04 in (cell c2) then (cell d2) would say 4.8-11.2 and (cell e2) would say 23.4 under target.

Column j,k and l convert reading ages to points and targets. So only want to type in column c then have column d and e auto filled.

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Commented May 20 at 14:34
• Welcome to Web Applications Stack Exchange. Do you already know about formulas, functions and cell and range references in Google Sheets? Commented May 20 at 14:34

Formula is placed in the headings row in cell D1 so it is separated from the data and isn't affected by sorts etc.

={"ZPD", "Termly Targets";
BYROW(OFFSET(C:C, 1, 0, XMATCH(TRUE, INDEX(C:C<>""),,-1)-1),
LAMBDA(r, IF(r<>"", LET(
val,IFERROR(r*1, SUMPRODUCT(IFNA(
REGEXEXTRACT(r, "(\d*)" & {"y";"m"}) / {1;100}))),
IFNA(FILTER(K:L, 1*J:J=val))),)))}

1. Clear column D and E and put formula in D1.
2. Column C values can be numbers or text strings like 12.06 or text strings where the years are followed by "y*" and the months are followed by "m*". For example, 12y6m, 12y 6months, 12yrs , 12y6momomomonths, etc.
3. Column J values can be numbers or numbers as strings. For example, 12, "12", 12.04, "12.04", etc.
• "isn't affected by sorts" — the C2:C100 reference in the formula will get changed when the sheet is sorted, and will no longer get the expected result. Start the reference from row 1 and leave it open-ended to avoid that. One way to do that would be with byrow(filter(C1:C, row(C1:C) > 1), .... Commented May 20 at 21:47
• Good catch! Ty! I've updated to use an open column reference and to remove empty rows after the last populated row using OFFSET(C:C,1,0,XMATCH(TRUE,INDEX(C:C<>""),,-1)-1). This avoids issues if there are empty rows within the data. Commented May 21 at 1:31

Use vlookup() and sort(). Clear cells D2:E and put this formula in cell D2:

=arrayformula(ifna(vlookup(
C2:C19, sort(J3:K), { 2, 3 }, true
)))


The values in cell C2:C and J3:J must be numbers like 15.04.

To place this formula in row 1 so that it is not affected by sorting the sheet and the like, clear columns C1:E, put "Years" in cell C1, and modify the formula like this:

=arrayformula(ifna(vlookup(
C1:C, sort(J3:K), { 2, 3 }, true
)))


See vlookup() and sort().