Forum Discussion
Excel Formula
Hi,
I'm trying to figure out which formula (if any) to use in my workbook to make it more automated. I am needing to have a formula in worksheet 4, that will go to worksheet 1, locate the word "Thursday" in column B and return the corresponding date listed in column A. For more context, worksheet 1 has information for daily testing and worksheet 4 is a report that requires specific information for Thursdays only. I've been able to automate everything except for pulling the dates from worksheet 1. I've tried VLOOKUP, XLOOKUP, IF, IFS and I just can't figure it out, as well as spending many hours trying to research which formula(s) to use. Please let me know if more information is needed.
Yes indeed; more information would be helpful. It's probably not a difficult thing to resolve, but seeing the actual workbook (or a fully representative mockup, if the actual contains confidential info) would make it a lot easier to help you.
That said, assuming that there probably are multiple "Thursday" entries on Sheet 1, and multiple corresponding dates, you might find the FILTER function does what you want. If you're comfortable playing with new functions, have at it yourself. That hyperlink provides some good examples. You might also find one of these YouTube videos to be useful.
But if help is still needed, come back and post a sample of your workbook, not an image, an actual Excel file.
4 Replies
The filter formula was the one that I needed. Thank you all for the quick response!
You can do this with either INDEX + MATCH or FILTER, depending on your version of Excel.
If you only need the first Thursday it finds, try:
=INDEX(Sheet1!A:A, MATCH("Thursday", Sheet1!B:B, 0))
That looks for the word “Thursday” in column B on Sheet1 and returns the date from column A in the same row.If you’re using Excel 365 and want all Thursday dates returned, you can use:
=FILTER(Sheet1!A:A, Sheet1!B:B="Thursday")
This will spill all matching dates into your Sheet4 report.If you need just one specific Thursday (like the most recent one), you could wrap it with MAX or MIN depending on whether you want the latest or earliest date.
Try the following formula-
Screenshot of the formula.=FILTER(Sheet1!A2:A1000,Sheet1!B2:B1000="Thursday","")Yes indeed; more information would be helpful. It's probably not a difficult thing to resolve, but seeing the actual workbook (or a fully representative mockup, if the actual contains confidential info) would make it a lot easier to help you.
That said, assuming that there probably are multiple "Thursday" entries on Sheet 1, and multiple corresponding dates, you might find the FILTER function does what you want. If you're comfortable playing with new functions, have at it yourself. That hyperlink provides some good examples. You might also find one of these YouTube videos to be useful.
But if help is still needed, come back and post a sample of your workbook, not an image, an actual Excel file.
