lookup data

Copper Contributor



trying to make this work:

In sheet A, i have 

 Column A: Start Date

 Column B: End Date

 Column C: Article A costprice

 Column D: Article B costprice

 Column E - T: Article C to Article R costprice


Every month, costprice changes (not for every article), so i adapt this manually in the correct Article Column.



In my Sheet B, i try to calculate the costprice per order/article.




I use nested IF functions to look for return value in column F, but this gets out of hand.


=IF(C2='kostprijs pallet'!$C$1;VLOOKUP(A2;'kostprijs pallet'!A:T;3);IF(C2='kostprijs pallet'!$D$1;VLOOKUP(A2;'kostprijs pallet'!A:T;4);IF(C2='kostprijs pallet'!$E$1;VLOOKUP(A2;'kostprijs pallet'!A:T;5);IF(C2='kostprijs pallet'!$F$...


* 'kostprijs pallet' = sheet A



Is there a formula i can use? One that looks in the correct period of time (delivery time) and selects the correct article?


1 Reply





Instead of nested ifs, I think you can use INDEX and MATCH functions to achieve this:. (example attached)

Screenshot 2023-11-13 at 2.53.31 PM.png