Multiply based on dropdown list conditions.

I think this is what you are looking for...
in I11 (dragged up and down) =VLOOKUP(J11,$A$3:$B$9,2,FALSE)
in Q11 (also dragged up and down) =P11*VLOOKUP(J11,$A$3:$C$9,3,FALSE)
other formulas such as index/match and xlookup would also work.

Hi Sam,

I have just been asked to add a few more things to this file, I am hoping you can help. I have already gone ahead and added the Data to the data sheet.

I would like the harvest date range to populate cell N2 based on the Cultivar chosen. It needs to be in this format (01-Jan-2024 - 04-Jan-2024). The harvest info is on the "Data" sheet in cells AD3 to AG24.

Here is the file in question: --NEW-- Crop Logs - Template.xlsx

Thank you in advance!!

Ok, this appears to work in N2...
=TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AD$3:$AD$24,,0,),"dd-mmm-yyyy")&" - "&IF(TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AG$3:$AG$24,,0,),"dd-mmm-yyyy")=TEXT(H1,"dd-mmm-yyyy"),TEXT(H1+XLOOKUP(H2,Data!$A$3:$A$24,Data!$AF$3:$AF$24,,0,),"dd-mmm-yyyy"),TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AG$3:$AG$24,,0,),"dd-mmm-yyyy"))
now, looking at the data tab I see that you have one "cultivar" that has a value for harvest day in column AH that wasn't included in your post #4, what do you want to do about that? Right now my formula ignores that.

you're welcome! and thanks for the rep!
it really isn't that complicated, it is just using the date in H1 and adding it to the numbers that are in AD3 through AG24 found using an XLOOKUP based on your dropdown in H2 and then when it finds the value in those columns and rows (AD3 through AG24) it adds the value to the date in H1 which will only come up as a number ... hence the text formatting with the way you want it using dd-mmm-yyyy and this part & - & combines it with the other value it finds to give you the date range you are looking for... 15-Jul-2024 - 18-Jul-2024
this part...
=TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AD$3:$AD$24,,0,),"dd-mmm-yyyy")&" - "&IF(TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AG$3:$AG$24,,0,),"dd-mmm-yyyy")=TEXT(H1,"dd-mmm-yyyy") is looking for what you want in AD through AG, and if it returns what is in H1 (text wise because H1 is only formatted to look like you want) it then goes to AF to look for the value using this...
TEXT(H1+XLOOKUP(H2,Data!$A$3:$A$24,Data!$AF$3:$AF$24,,0,),"dd-mmm-yyyy")
otherwise it returns what is in AG with this final part of the if statement... TEXT(H1+XLOOKUP($H$2,Data!$A$3:$A$24,Data!$AG$3:$AG$24,,0,),"dd-mmm-yyyy"))

what took me so long was getting the syntax/sequence of the true and false parts of the if statement to return the correct values.

To get around the #SPILL! error:

=TAKE(TOCOL(P1*0.5/Q5:Q84,3),1)

Otherwise, this thread is marked as solved and the new requirements are different - please start a NEW thread for the 'last' question. Thanks.

Great stuff!