I'm building a sheets with the following pages:
- Records
- Team
Records Page:
| Date | Time | Where | User | Pay |
|---|---|---|---|---|
| 01/01/2022 | 01:40 | Warehouse | Mike Silva | 14,00$ |
| 04/01/2022 | 07:40 | Main Office | Mike Silva | 15,00$ |
Team Page:
| Date | User | Pay |
|---|---|---|
| 01/01/2022 | Mike Silva | 14,00$ |
| 03/01/2022 | Mike Silva | 15,00$ |
Up until the 3rd of January Mike had a pay of 14,00$ and from the 3rd onwards is pay was bumped to 15$. I've used INDEX/MATCH to get the pay within the correct date, but I can't get it into a ArrayFormula to auto update all cells.
How would you approach this?
CodePudding user response：
Got the answer after some trial/error.
On team page I created a helper column (User Date):
| Date | Helper | User | Pay |
|---|---|---|---|
| 01/01/2022 | Mike Silva|44562 | 14,00$ | Mike Silva |
| 03/01/2022 | Mike Silva|44564 | 15,00$ | Mike Silva |
Then, i just:
=ARRAYFORMULA(PROCV(USER&"|"&DATE;Team!B2:D;3;TRUE))
Thank you so much for your help!
CodePudding user response：
each character has its own CHAR code:
so first we convert names into numbers:
=ARRAYFORMULA(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IFERROR(VLOOKUP(
SPLIT(REGEXREPLACE(A1:A4, "(?:)(.)", "$1 "), " "),
{CHAR(ROW(44:122)),SEQUENCE(79)}, 2, 0))),,9^9)), " ", ))
and next, we sum it and just add date and run approximate vlookup:
=ARRAYFORMULA(IFERROR(VLOOKUP(
MMULT(IFERROR(VLOOKUP(SPLIT(REGEXREPLACE(FILTER(D1:D, D1:D<>""),
"(?:)(.)", "$1×"), "×"), {CHAR(ROW(44:122)), SEQUENCE(79)}, 2, 0))*1,
SEQUENCE(MAX(LEN(D1:D)), 1, 1, 1))*A1:A, {
MMULT(IFERROR(VLOOKUP(SPLIT(REGEXREPLACE(FILTER(H2:H, H2:H<>""),
"(?:)(.)", "$1×"), "×"), {CHAR(ROW(44:122)), SEQUENCE(79)}, 2, 0))*1,
SEQUENCE(MAX(LEN(H2:H)), 1, 1, 1))*G2:G, I2:I}, 2, 1)))
update
or just:
=INDEX(VLOOKUP(D1:D&A1:A, SORT({H2:H&G2:G, I2:I}), 2, 1))
