Forum Discussion
Help Formula outcome of two cells in combination with dropdown is value over more columns
Goedendag.
I have revised your sheet and inserted formulas in the area ='Calculator'!G16:G26. These first look in an auxiliary area ('wm60'!C$235:E$255) that I created where the price data is. Then it is determined which line in the respective price table comes into play. By comparing the table headings and the quantities in the area ='Calculator'!G15:K15, the correct quantity price is then extracted for each quantity area and multiplied by the quantities.
Unfortunately, exactly for the example you chose, the price table was not available. Therefore, I am not quite sure whether I have done everything correctly. So please check again very carefully and critically.
=IFERROR(LET( aerea,FILTER('wm60'!C$235:E$255,'wm60'!A$235:A$255=F16), price_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,2),,1)), row_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,3),,1)), header,INDEX(price_aerea,1,0), ro_w,XLOOKUP(E16,row_aerea,price_aerea,,1), IFNA(ROUND(XLOOKUP(G$15:K$15,header,ro_w,,-1)*G$15:K$15,2),"x")),"")I hope the calculation is correct and the table helps you now. If you need more help, please let me know.
Thank you so much for your help, super!
I don't understand the formula so if you don't mind i wanna send you the original.
Hope this clarefy it for you and solves my problems
You see in "list" how i have manually calculate the BE... cardboard, maybe it's easy for you to implement this in the calculator?
P.S. the formula is in dutch
Hope you can help.
Nevertheless I am grateful for your help!
Goedendag.
I have revised your sheet and inserted formulas in the area ='Calculator'!G16:G26. These first look in an auxiliary area ('wm60'!C$235:E$255) that I created where the price data is. Then it is determined which line in the respective price table comes into play. By comparing the table headings and the quantities in the area ='Calculator'!G15:K15, the correct quantity price is then extracted for each quantity area and multiplied by the quantities.
Unfortunately, exactly for the example you chose, the price table was not available. Therefore, I am not quite sure whether I have done everything correctly. So please check again very carefully and critically.
=IFERROR(LET(
aerea,FILTER('wm60'!C$235:E$255,'wm60'!A$235:A$255=F16),
price_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,2),,1)),
row_aerea,INDIRECT("'wm60'!"&XLOOKUP(E16,INDEX(aerea,0,1),INDEX(aerea,0,3),,1)),
header,INDEX(price_aerea,1,0),
ro_w,XLOOKUP(E16,row_aerea,price_aerea,,1),
IFNA(ROUND(XLOOKUP(G$15:K$15,header,ro_w,,-1)*G$15:K$15,2),"x")),"")I hope the calculation is correct and the table helps you now. If you need more help, please let me know.