Forum Discussion
Help with XLookup to change Search Ranges per a Selected Condition
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 The 3 is the wrap_count argument of the WRAPROWS function, which is set to 3 because the Conditions in your sample file contain 3 columns each. Basically, XLOOKUP returns a single row spanning across all 3 Conditions for the matching Unit. WRAPROWS then converts the 1 x 9 vector into a 3 x 3 array, and CHOOSEROWS returns the correct row for the matching Condition (using XMATCH).
You could also use the LET function to optimize the formula a bit further and make it easier to read/manage:
=LET(
vector, XLOOKUP(D18, B6:B12, C6:N12),
rowId, XMATCH(C18, TOCOL(F3:N3, 1)),
cols, 3,
CHOOSEROWS(WRAPROWS(DROP(vector,, cols), cols), rowId) + TAKE(vector,, cols)
)
In this example, only one XLOOKUP formula was used to return the entire row for the matching Unit (including the Base Stats and Augmented Stats for all Conditions), then DROP returns the Augmented Stats and TAKE returns the Base Stats. The cols variable refers to the number of columns per Condition. If your actual data set contains 8 columns of Stats per Condition, change cols from 3 to 8.
Regarding the INDEX function, when a 0 is provided for the [column_num] argument (or if it's omitted), INDEX will return all columns in the range.
I would go with the CHOOSEROWS/WRAPROWS/XLOOKUP method shown above, over the INDEX method. If your actual data set contains 8 different Conditions, the range reference for INDEX will need to include all 8 areas separately, whereas the return_array for XLOOKUP need only be a single range.
I hope that all makes sense. Cheers!
