Help with XLookup to change Search Ranges per a Selected Condition

[CADDmanDH]

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 force the wrong formula to do what I need. I created an example of my much larger workbook that should be sufficient to show what I'm going for, which is to add a Value to a Base Unit Stat Value, dependent upon a selected condition. In short I don't know how get a Formula to search through the Title, Sub-Title to add that Value based on the Selected Condition and Unit.

Thank you!

[#2]

@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))

[#3 · Accepted solution]

@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...

[#4]

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?

[#5]

@djclements 

Thank you for your response! I was able to better follow along with these suggestions. The Bottom two rows of Examples is what I am thinking to choose from to attempt to implement with. I have 19 different Units, 8 Rows of Stats, and 8 different Conditions. I haven't used Index, ChooseRows, or Wraprows yet. I would have never figured this out given the fair complexity, though succinct formulas.

My follow up question perhaps is, what does the 3 Represent here? the number of columns?

=CHOOSEROWS(WRAPROWS(XLOOKUP(D18, B6:B12, F6:N12), 3), XMATCH(C18, A16#)) + XLOOKUP(D18, B6:B12, C6:E12)

Equally, what does the 0 represent here in both locations?

=INDEX((F6:H12,I6:K12,L6:N12), XMATCH(D17, B6:B12), 0, XMATCH(C17, A16#)) + INDEX(C6:E12, XMATCH(D17, B6:B12), 0)

Which of these two Formulas would be preferred? Are there Pros & Cons to either? One easier to work with over the other?

Thank you again for your Help!

[#6]

@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!

[#7]

Yes Thank you! That was the one I decided to implement last night and it Works great. I just saw your addition of LET, but I will probably leave it the way it is with the initial ChooseRows Solution. Thank you so much, that was all a huge help!