I think that proprer formula for L6 could read:
=IF(VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)="1 kg",J6,I6)*K6
If you want to make it more developed you could use:
=IF(VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)="1 kg",J6*K6,IF(VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)="1 m3",I6*K6,"Unknow unit"))
or may be:
=IFERROR(IF(VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)="1 kg",J6,I6)*K6,"")
In all examples above I used absolute addressing of B4:E52 range in sheet Tabell energislag - you can copy the cell with such formula to cells L9 L12 etc without editing it - G6, J6 etc will change to G9, J9 ... or G12, J12 ... but reference to B4:E52 will remain unchanged.
In the attached file I also added 1 column with units (just for information purposes - can be deleted without any side effects) with formula: =VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)
I also made some conditional formatting of cells with kg/m3 (Polumns I and J) - see what hapens with cell interior colors if uou write value in proper cell or in wrong one. And then go to Conditional Formating and Rules Management to see rules I used. (Just to see more excel advantages :-D )
Bookmarks