Forum Discussion
Compound If statements
Life would become far easier if you could set your Master Data up so that it is searchable rather than hard-wiring the links between sheets manually. For example
= LOOKUP( 1,
1 /(Packages[CP Code]=Partner)
/(ISNUMBER(SEARCH(Level, Packages[CP Packages]))),
Packages[AW Price] )
You will also need to include a test on 'Setup', 'Maintenance' etc.
PeterBartholomew1 Thank you for your reply.. are you saying my Master Data is not setup efficiently? Most of my cells are dependent on 2 - 3 criteria. It would be much appreciated if you could provide the LOOKUP formula for cell G18 & G21 so I may use it as an example. Your help is much appreciated. Thank you.
I'd do not rely on direct lookups since texts to lookup could vary (e.g. Package (C) = PACK C, etc.). Perhaps parallelly to data validation lists
create one more range which maps above on position of cell to pick-up. Or even better to link here cells from which values ae to be taken, with that we are less dependent from insertion/deleting rows in main list.
But it very depends on how information about the packages is updated - is it fixed structure or not, does text in CP Packages are exactly the same all the time or they could vary (Package(C) = PACK C =
PACKAGE C = pkg C = etc).
Other words, it's not enough knowledge of business logic on our site (which could be obvious for you).
I think we are in agreement.
The nested IFs look a nightmare to me; I think I might even prefer to copy/paste the values across from the second sheet than try to validate the formulas!
It's possible to survive with complex nested IF generating it first time. Perhaps even second time to modify the logic in initial formula. But on third time you start thinking that maybe it'll be better to change the job.
What I had in mind was that entries like
MO MAINTENANCE PACK C; includes B2B
are designed to be informative to the reader but it is not so easy to determine whether it is an exact match for a criterion value from the overview sheet. For searching you need fields which contain specific key words such as SETUP or MAINTENANCE which allow for searches that return an exact match. Additional information that does not form part of the search criteria should be restricted to comment fields that are not referenced by formulas.
I would normally use XLOOKUP to search for data that matches a combination of criteria but that requires Excel 365, so I have used LET in your workbook.
