Forum Discussion
Match formula with alternate simple math calculation
I guess it would be too much to hope that you are using Excel 365? Most of the complexity of your formula just melts away. You would get some suable structured references if you made the data into Tables. You are searching for a specific brand and variety; defined names for those would make the formula far more intelligible.
CHOOSE could be used to return the correct table before searching so the search need not be repeated. This is of most value within a LET formula but can be used at the core of a nested formula.
XLOOKUP would return the correct columns more transparently than OFFSET.
The INDEX to return the array of matched rows would not appear to do anything by return the Boolean array you already have. Using XLOOKUP for this search as well provides a parameter to return when the search fails.
Now I know why I have given up on legacy spreadsheets!
I got as far as
= LET( option, 1+OR(BrandSelected={"CAN.INTL","BULK"}), Table, CHOOSE(option,BulkTbl, BulkTbl), Headings, CHOOSE(option, DomesticTbl[#Headers], BulkTbl[#Headers]), Brand, XLOOKUP("Brand", Headings, Table), Variety, XLOOKUP("Variety", Headings, Table), filteredvarieties, IF(Brand=BrandSelected, Variety), ReturnCol, XLOOKUP(@ColumName, Headings, Table), Default, 100-CROP-INERT-WEED, XLOOKUP(VarietySelected, filteredvarieties, ReturnCol, Default) )but then I noticed that the default values of 100-CROP-INERT-WEED are also lookups that will fail when the main lookup fails.
- I do have 365. Is what you are saying above to use with tables like you were saying would work better? If I did put the data into tables, what would happen to all the other formulas in my spreadsheet - would they all have to be re-worked?
Tables are of the greatest value when there exists the possibility of adding data to them. The structured references they provide are dynamic so there is no need to work through the solution adjusting downstream formulas. Since a defined name is simply a name given to a formula, it is possible to create names to refer to the structured references if that shortens the formula or makes it more readable.
The use of names for some formulas does not impact the other, more traditional, formulas using direct referencing to single cells (as relative references). If the workbook were mine, though, I would get rid of every last direct reference, but most other contributors to this forum are happy to retain the traditional approach.
That said, I have used a number of techniques for the lookup that you may wish to consider. Firstly there is the use of CHOOSE to select the appropriate Table. This is narrowed down by using XLOOKUP to return a column from the table (though there are instances where it is better to lookup the row first and return an entire record),. An advantage of the second lookup is that there is no need to wrap the function with IFERROR because it has a built-in error handling capability.
Good luck.
