Fórmula genérica
=XLOOKUP(max,dates,results,,-1) // latest match by date
Resumo
Para obter a correspondência mais recente em um conjunto de dados por data, você pode usar XLOOKUP no modo de correspondência aproximada definindo match_mode como -1. No exemplo mostrado, a fórmula em G5, copiada, é:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
em que data (C5: C15), item (B5: B15) e preço (D5: D15) são intervalos nomeados.
Explicação
XLOOKUP oferece vários recursos que o tornam excepcionalmente bom para pesquisas mais complicadas. Neste exemplo, queremos o preço mais recente de um item por data. Se os dados fossem classificados por data em ordem crescente, isso seria muito simples. No entanto, neste caso, os dados não são classificados.
Por padrão, XLOOKUP retornará a primeira correspondência em um conjunto de dados. Para obter a última correspondência, podemos definir o argumento opcional search_mode como -1 para fazer com que o XLOOKUP pesquise "do último ao primeiro". No entanto, não podemos usar essa abordagem aqui porque não há garantia de que o preço mais recente de um item apareça por último.
Em vez disso, podemos definir o argumento opcional match_mode como -1 para forçar uma correspondência aproximada de "exata ou próxima menor" e ajustar o valor de pesquisa e a matriz de pesquisa conforme explicado abaixo. A fórmula em G5, copiada, é:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Trabalhando com os argumentos um por um, o lookup_value é a maior (mais recente) data nos dados:
MAX(date) // get max date value
O lookup_array é derivado de uma expressão lógica booleana:
(item=F5)*date
Ao comparar cada item com o valor em F5, "Faixa", obtemos uma matriz de valores VERDADEIRO / FALSO:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
onde os valores TRUE representam entradas para "Belt". Este array atua como um filtro. Quando é multiplicado pelos valores no intervalo de data nomeado , os valores VERDADEIRO / FALSO são avaliados como 1 e 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
O resultado é uma matriz que contém apenas zeros e datas para cintos:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Nota: os números de série são datas válidas do Excel.
Esta matriz é entregue diretamente ao XLOOKUP como o argumento lookup_array.
O return_array é a faixa de preço nomeada (D5: D15)
O argumento opcional not_found não é fornecido.
Match_mode é definido como -1, para correspondência exata, ou próximo menor item.
XLOOKUP examina a matriz de pesquisa para o valor máximo de data. Uma vez que a matriz já foi filtrada para excluir datas não associadas a "Belt", XLOOKUP simplesmente encontra a melhor correspondência (a data exata ou a próxima menor data) que corresponde à data mais recente.
O resultado final é o preço associado à última data. A fórmula continuará a funcionar quando os dados forem classificados em qualquer ordem.
