excelexcel-formulavlookupxlookup

Excel Lookup with repeated values in Range

Hello and thanks in advance!

I am trying to find a way to lookup values (without going into vba).

I have a table like this with hundreds of rows. (Excel 365)

enter image description here

In another sheet, I have this summary where I need to get the "Value" of each "Rank" for each "Vessel". enter image description here

This could have been a simple Vlookup but the problem is that there are multiple "OS" and "OLR" for each vessel. How do I get the first OS's value in the first OS column and the second OS's in the second column?

Any help or guidance is much appreciated!

I tried looking into XLookup as well but it doesnt mseem to cover my use case.

Solution

  • Using one single dynamic array formula one could accomplish this task, it uses a LAMBDA() helper function to iterate over each criteria and perform the function assigned to it.

    enter image description here

    • Formula used in cell F2

    =LET(
     a, A2:A11, b, B2:B11,
     c, C2:C11, d, E2:E5,
     e, F1:O1, _r, ROWS(d),
     _c, COLUMNS(e),
     MAKEARRAY(_r,_c,LAMBDA(x,y, IFERROR(INDEX(TOROW(FILTER(c,(a=INDEX(d,x))*(b=INDEX(e,y)))),
     COUNTIF(INDEX(e,1):INDEX(e,y),INDEX(e,y))),""))))

    Or, If one wants to use fill down and fill right then,

    =IFERROR(INDEX(TOROW(FILTER($C$2:$C$11,($E2=$A$2:$A$11)*(F$1=$B$2:$B$11),"")),
  COUNTIFS($F$1:F$1,F$1)),"")