Search code examples
excelexcel-formulareturnmaxvlookup

Vlookup based on one column but return value based on other column with Max ID using Excel formula

enter image description here

enter image description here

From 1st file, using vlookup function in excel where I have only Fill ID in second file , how do I get request type based on Max ID (Column name ID) ?

Solution

  • Try using the following formula:

    enter image description here

    =LET(
     _Data, A3:D5,
     _ColumnsNeeded, CHOOSECOLS(_Data,1,4),
     @TAKE(SORT(FILTER(_ColumnsNeeded,INDEX(_Data,,3)=F3),,-1),,-1))

    Fundamentally it is one single formula I just tried to break it down in above:

    enter image description here

    =@TAKE(SORT(FILTER(A3:D5,F3=C3:C5),,-1),,-1)

    Or using VLOOKUP() with MAXIFS() note this doesn't takes into account of duplicate max values:

    enter image description here

    =VLOOKUP(MAXIFS(A3:A5,C3:C5,F3),A3:D5,4,0)