Forum Discussion

Copper Contributor

Jul 19, 2021

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

11TH BAGGING WO BAGS (OREC1008) NEW UPDATE 7.16_sample.xlsx346 KB

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Jul 20, 2021

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.

kdwork

Copper Contributor

Jul 22, 2021

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?