Forum Discussion

CADDmanDH's avatar
CADDmanDH
Copper Contributor
Apr 28, 2024

Help with XLookup to change Search Ranges per a Selected Condition

Hello Xcel Pros,      I've used XLookup for a number of things, but I can find, figure out, or wrap my head around what I think could be easy enough to do, but I am just missing it, or I'm trying to...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Apr 28, 2024

    CADDmanDH Another option is to use the secondary syntax of the INDEX function to reference multiple areas (reference, row_num, [column_num], [area_num])...

     

    =INDEX((F6:H12,I6:K12,L6:N12), D16, 0, C16) + INDEX(C6:E12, D16, 0)

     

    ...where the Unit Selector (numeric value from 1 to 7) is the row_num and the Condition Selector (numeric value from 1 to 3) is the area_num.

     

    If you prefer the Unit/Condition Selectors to reference the row/column labels, you will need to use the MATCH or XMATCH function to return the corresponding row_num and area_num.

     

    See the attached workbook for additional examples...

Harun24HR's avatar
Harun24HR
Bronze Contributor

CADDmanDH You need nested FILTER() function with help of few other functions.

=CHOOSECOLS(FILTER(FILTER($F$6:$N$12,$B$6:$B$12=$D$16),SCAN("",$F$3:$N$3,LAMBDA(a,x,IF(x="",a,x)))=$C$16),COLUMN(A$1))

 

 

CADDmanDH's avatar
CADDmanDH
Copper Contributor
Apr 29, 2024
Thank you, this looks impressive. Certainly nothing I would have thought of. Apologies if I didn't make clear, but from the formula that you have, how would you add the Base Stats (the grey area) to that of the Augmented Stats under the selected Condition?