Handling error condition in VLOOKUP, plus …

I changed the formula for column A, moved column B to the right, and recreated a column B for dayname.

Work Record table formulas:

F2 =YEAR(A2)×10000+MONTH(A2)×100+DAY(A2)+TIMEVALUE(B2)

G2 =ROW()

Fill down with both to complete the columns.

Table 1 cells and formulas:

A2 is the date you want to start with

G1 is 0

A3=IF(COUNTIF(A$2:A2,A2)<COUNTIF(Work Records::A,A2),A2,A2+1)

C2 =IF($F2=0,"",INDEX(Work Records::B,$F2))

fill right to E2 then change the column reference in the E2 formula to be the "client" column

F2 =MINIFS(Work Records::G,Work Records::F,">"&G1,Work Records::A,A2)

G2 =YEAR($A2)×10000+MONTH($A2)×100+DAY($A2)+IF(F2≠0,TIMEVALUE(C2),0)

Fill down to complete the columns

Hide columns F and G in both tables

I've got something that appears to be working but I want to make sure it actually does and also make it better if I can. It is kind of complicated; I am very likely going about it in a harder way than necessary. It is a first draft, proof of concept. Here it is so far.

Work Record table requires two additional columns with formulas:

F2 =YEAR(A2)×10000+MONTH(A2)×100+DAY(A2)+TIMEVALUE(B2)

G2 =ROW()

Fill down with both to complete the columns.

Table 1 (the calendar table) formulas and cells are:

F1 is a 0

A2 is the first date you want in the table

A3 =IF(MINIFS(Work Records::G,Work Records::F,">"&F2,Work Records::A,$A2)=0,$A2+1,$A2)

fill down to complete the column

B2 =MINIFS(Work Records::G,Work Records::F,">"&F1,Work Records::A,A2)

fill down to complete the column

C2 =IF($B2=0,"",INDEX(Work Records::B,$B2))

Fill across to E2. You'll have to adjust the formula in E2 so it gets its date from the correct column

Fill down with these three formulas

F2 =YEAR(A2)×10000+MONTH(A2)×100+DAY(A2)+IF(B2≠0,TIMEVALUE(C2),0)

Fill down to complete the column

I should have left column B as the day name and put the row calculation in a column to the right. I'll move it in the next iteration.

Here is it.

Neither table needs the column for the row number. I think I could have gotten away without it before, too. The formula in Work Records column F is the same as before.

Table 1:

Column A is dates. No formulas. Enter the first date then drag down.

L2 =YEAR($A2)×10000+MONTH($A2)×100+DAY($A2)

M2 =MINIFS(Work Records::$F,Work Records::$F,">="&L2,Work Records::$A,$A2)

N2 =IF(M2≠0,MINIFS(Work Records::$F,Work Records::$F,">"&M2,Work Records::$A,$A2),0)

Fill right from N2 into O2.

Fill down with all to complete these columns

C2 =XLOOKUP($M2,Work Records::$F,Work Records::B,"",0)

Fill right to E2 then change the column reference in E2 so it looks up the client, not duration

F2 =XLOOKUP($N2,Work Records::$F,Work Records::B,"",0)

Same thing like you did with C2

I2 =XLOOKUP($O2,Work Records::$F,Work Records::B,"",0)

Same thing like you did with C2

Fill down to complete columns C-K

Hide columns L-O

WKH wrote:

How do I address the errors that appear in the Teaching Calendar table, when there is no entry in the Teaching Tracking table for a given day (i.e., something like the ISNA function in Excel)?

2. How do I address the fact that only the first entry for a given day date is reflected in the Calendar? (See, for example, 20th January, when the first lesson started at 10:00 and lasted for 3 hours, and the second started at 13:00 and lasted for 2 hours. Only the first lesson is reflected in the Calendar.)

I don't have a full solution, but here are some suggetions:

Use the more modern XLOOKUP instead of VLOOKUP. XLOOKUP allows you to define an if-not-found value.

XLOOKUP - Apple Support

Separate your month calendars into separate tables. As you know, Numbers makes it easy to have multiple tables on one sheet.

SG

Handling errors is pretty easy. You can use IFERROR or you can use XLOOKUP instead of VLOOKUP. XLOOKUP has a parameter that lets you specify the result if not found. The much harder part is finding and returning multiple matches. No lookup function has that capability. Until that problem is solved, there's not much point in rewriting your formulas using XLOOKUP or IFERROR.

There are known ways to list all matches for a single specified date but nothing that I know of that would list all matches for a specified date then move on to the next date and so on. It might take a little while to create that. I am thinking if the Work Records table had a new column that put the date and time together as a numeric value, it might be possible to use MINIFS to locate each unique row and XLOOKUP or OFFSET or something to return the results. Incrementing the date in column A of the Teaching Calendar table will also require some kind of formula.

One question first: In the Teaching Calendar is it necessary that it all dates be listed, that every day of the year has a row, even those with no Work Record data? If all you need are the work records, it would be much simpler to do everything in the Work Record table or to use simple formulas (such as A2 =Work Record::A2) to bring all the work records into this table.

It can probably be done. Actually, it will probably be a lot easier because we won't have to figure out the date for each row, the dates will be sequential. It will have a limit to how many "entries" you can have in a day and each entry will require a few columns (at a minimum the columns for start and eSo it will be limited in how many entries per day maximum. I doubt having a "limit" is a problem, you can add enough columns for 50 entries per day if you want to. I'm too busy to work on it right now but I'll give it some thought.