Forum Discussion
Match formula with alternate simple math calculation
Good morning. PLEASE help. I have a formula that almost does everything I need it to do...I have a spreadsheet (sample attached) that I have to have the data that it is pulling from on two separate tabs for internal reasons. (Trust me, I know if the data was all on one tab it would be sooo much easier - I had it that way originally) I need the formula that is in R9 of the 'New Office Copy' tab to perform two functions for the tab 'Product List ITL_Bulk'. The 1st is what it is doing now - going to the tab 'Product List ITL_Bulk' and doing a match for S9. What I also need it to do is if it doesn't find a match for S9 is to then do a math calculation - (100-R10-R11-R12).
If you go to row C6 and hit the drop down and choose the next variety 106T701 CRT BK, you will see what I need it to do (100-R10-R11-R12)
The tab Product List ITL_Bulk is used for cell E3 when you choose the dropdown for CAN.ITL and BULK. All the others on that drop down use the tab Product List-Domestic. I need the operation in the formula for the tab Product List-DOMESTIC to remain unchanged.
Hopefully this makes sense? It is sometimes hard to explain what you are wanting/needing.
This is the formula I currently have.
=IF(OR($E$3={"CAN.INTL","BULK"}),IFERROR(OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$722)*($C$6='Product List ITL_Bulk'!$D$2:$D$722),,),0),MATCH($S$9,'Product List ITL_Bulk'!$A$1:$CB$1,0)-1,1,1),100-R10-R11-R12), IFERROR(OFFSET('Product List-DOMESTIC'!$A$1,MATCH(1,INDEX(($E$3='Product List-DOMESTIC'!$A$2:$A$605)*($C$6='Product List-DOMESTIC'!$D$2:$D$605),,),0),MATCH($S$9,'Product List-DOMESTIC'!$A$1:$CB$1,0)-1,1,1),100-R10-R11-R12))
10 Replies
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?
kdwork I am not even going to try decipher that formula, but it seems that it evaluates to zero based on either of the IFERROR functions. Why not include (100-R10-R11-R12) in stead of 0 in these IFERROR functions?
- I am not sure which 0 you are saying to replace?
kdwork For instance, you have this:
IFERROR(OFFSET('Product List ITL_Bulk'!$A$1,MATCH(1,INDEX(($E$3='Product List ITL_Bulk'!$A$2:$A$722)*($C$6='Product List ITL_Bulk'!$D$2:$D$722),,),0)The 0 at the end is returned if the OFFSET function is generating an ERROR. When I selected the next option in the dropdown as you suggested, I got a zero. So, I suspected that one of the three IFERROR functions caused this and wondered if you could perhaps replace each of these zeros with the alternative formula (100-R10-R11-R12). Just an idea.
