Forum Discussion
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...
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...
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))
- 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?
