Excel Lookup with repeated values in Range

Question

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)

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

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.

Answer 1

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.

---

• 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)),"")

---

Answer 2

Not really the question, but may be useful:

=LET(v,A2:A11,
     r,B2:B11,
     vr,v&r,
     n,SCAN(,SEQUENCE(ROWS(vr)),LAMBDA(a,b,LET(c,SEQUENCE(b),MMULT(N(INDEX(vr,TOROW(c))=INDEX(vr,b)),c^0)))),
VSTACK(
       HSTACK(A1,
              TOROW(B2:B11)),
       HSTACK(UNIQUE(v),
              IF(UNIQUE(v)&TOROW(r&-n)=TOROW(vr&-n),TOROW(C2:C11),""))))

This will spill the pivot of the unique VSL vs the Rank values with their corresponding dates.

Or in Beta Insider: =PIVOTBY(A1:A11,B1:B11,TEXT(C1:C11,"yyyy-mm-dd"),ARRAYTOTEXT,,0,,0)

A summary of unique VSL and unique Rank and their associated values comma separated.