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

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

- CADDmanDHCopper ContributorThank 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?

- djclementsBronze Contributor
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...

- CADDmanDHCopper Contributor
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!

- djclementsBronze Contributor
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!