CODEWITHSUNDEEP
excelexcel-formula
SM Zia
SM Zia

Reputation: 1

Lookups with multiple criteria in different sheets (Excel/Sheets)

https://docs.google.com/spreadsheets/d/14DjVgC_WYKSOYyDJwwlDOq45f1Ku8Op9/edit?usp=sharing&ouid=108133721563589506729&rtpof=true&sd=true

Above is the link for the file and I am trying to fetch data from Greige Data Sheet into individual sheets. I tried indexing by taking help from online resources available but couldn't get any benefit from it.

Upvotes: 0

Views: 117

Answers (1)

JB-007
JB-007

Reputation: 2441

Screenshots/here refer:

Caveats

  • Functions require Office 365 compatibility (not nec. 100% compatible with Sheets - untested)
  • Functions longer than usual due to inconsistent table header names and label names in respective worksheets (e.g. "METTER" vs "METER" etc.) and light testing on my part RE: let parameter/fns

Beam # (cell B14)

=LET(x_,SORT(UNIQUE(FILTER(Table_13[BEAM],--(1*Table_13[LOT]=1*$J$3)))),y_,INDEX(x_,(COLUMNS($A14:B14)/2),1),y_)

Beam #

Notes: copy / paste to D14, F14 etc. as req.

LOOM # (cell B13, D13, etc.)

=INDEX(Table_13[LOOM],MATCH(B14,Table_13[BEAM],0))

Loom #

notes: as before

DOFF # & METER #

=LET(x0_,IF(A14="",1*B14,IFERROR(1*A14,1*B14)),x_,FILTER(Table_13,--(1*Table_13[LOT]=1*$J$3)*(Table_13[BEAM]*1=x0_)),a_,1*ROWS(A17:A25)*(COUNTIFS($B14:B14,x0_)-1)/2,y_,INDEX(x_,SEQUENCE(MIN(ROWS(A17:A25),ROWS(x_)-a_),1,a_+1,1),MATCH(MID(A16,1,3),MID(Table_13[#Headers],1,3),0)),z_,IFERROR(y_,""),z2_,FILTER(z_,--(y_<>"")),z3_,IFERROR(z2_,""),z3_)

Doff & Meter

notes

  • copy / paste into B17,C17,.. etc. (i.e. same function works for both Doff and Meter lookups)
  • Array / spill function (i.e. only need to apply to first cell eg. A17, required list will 'spill')
  • dependent upon respective Lot# & Beam # (e.g. Doff/Meter in cols A,B relate to Beam # cell B14; C,D relate to D14 etc.) - Beam #dependent upon Lot number (cell J3 I think)
  • Additional feature: if insufficient rows avail. for all dofs/meters assoc. with a given beam #, entering the same beam # will allow the respective lists to continue as req. (i.e. without repeating pvs doff/meters for the beam# in Q).
  • Assumes distinct loom # for each beam #
  • Unsure how to produce Beam Length given the data provided (same goes or various other values, e.g WEFT etc.)

Upvotes: 1

Related Questions