Função agregada sobre VLookup no intervalo

Question

Existe uma maneira de obter um valor máximo de um intervalo de valores usando funções padrão do Excel?

Mais informações

• Eu quero encontrar o valor máximo em um intervalo de células. A1: =max(A2:A10)
• No entanto, não quero encontrar o máximo do valor nas próprias células, mas sim de um conjunto associado de valores obtido ao fazer um vlookup nessas células. ou seja, B2: =vlookup(A2,MyLookupName,2,false) (copiar a fórmula B2 para B10). A1: =max(B2:B10) .
• Ao invés de ter que fazer isso em 2 etapas (ou seja, fazendo o vlookup na coluna B, então fazendo o máximo sobre o que na célula A1) eu prefiro ter uma única fórmula que aplicou os dois passos em um, na mesma célula . ou seja, A1: {=max(vlookup(A2:A10,MyLookupName,2,false))}

No entanto, esta última fórmula não funciona, já que a função VLOOKUP aceita apenas uma única célula para o parâmetro Lookup_Value ; se um intervalo for passado, ele levará a célula superior esquerda desse intervalo.

Solução VBA

Para ilustrar melhor a funcionalidade que estou procurando, codifiquei uma função de planilha do VBA que faria o que eu queria; no entanto, estou tentando evitar macros personalizadas.

Public Function MaxVLookup(ByRef Lookup_Array As Range, ByRef Table_Array As Range, Col_Index_Num As Long) As Variant
    Dim v As Variant, temp As Variant, max As Variant
    max = Null
    For Each v In Lookup_Array
        temp = WorksheetFunction.VLookup(v, Table_Array, Col_Index_Num, False)
        If IsNull(max) Then
            max = temp
        Else
            If max < temp Then
                max = temp
            End If
        End If
    Next
    MaxVLookup = max
End Function

    

Answer 1

Se os valores em A estiverem na Área de Referência somente uma vez, você poderá usar SUMUIF () empacotado em um MAX () como uma fórmula de matriz:

=MAX(SUMIF(D:D,A1:A10,E:E))

Sendo uma fórmula de matriz, ela precisa ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo Editar. Se feito corretamente, o Excel colocará {} em torno da fórmula.

SeelessãomúltiplosevocêquerapenasoprimeirocomoVLOOKUP,useLOOKUP():

=MAX(IFERROR(LOOKUP(A1:A10,D1:D13,E1:E13),-1E+99))

Maisumavez,estaéumafórmuladematriz.

Sendoumafórmuladematriz,elaprecisaserconfirmadacomCtrl-Shift-EnteremvezdeEnteraosairdomodoEditar.Sefeitocorretamente,oExcelcolocará{}emtornodafórmula.

Capturadetelacombasenoexemplo

    

Answer 2

NB: encontrei uma solução alternativa para o meu problema. Como eu só precisava mapear cada valor para um número, fui capaz de falsificar o VLookup usando o Match.

i.e. A1: {=max(6-match(A2:A10,MyLookupName,0))} .

O 6- é simplesmente alterar o valor retornado pela correspondência (isto é, a posição do elemento que estou procurando na lista de valores MyLookupName ) para o valor que estou mapeando.

Screenshot de demonstração

Solução com valores:

Soluçãocomfórmulas:

NB: Eu não marquei esta como uma resposta, uma vez que, embora tenha resolvido o meu problema, não resolve todos os problemas deste tipo / ignora a resposta à pergunta que fiz.