Forum Discussion
Fetch data depending on value in list
Let's say your data are on Sheet 1 in A2:D100, with items in column A and categories in column D.
On the other sheet, the category clothes is in B1.
In another cell:
=FILTER('Sheet 1'!A2:A100, 'Sheet 1'!D2:D100=B1, "No Match")
Thank you so much! Let's say I have one column with dates and I only want the ones with a certain month (or months) to show, can I do that as well?
With dates in F2:F100:
=FILTER('Sheet 1'!A2:F100, ('Sheet 1'!F2:F100>=DATE(2024, 10, 1))*('Sheet 1'F2:F100<=DATE(2024, 12, 31)), "No Match")
will return the rows with dates in October, November and December of 2024.
You have been very helpful!
Let's say I on top of that would like Excel to fetch data from two different sheets, would that be possible?So for example, all of the above would be the same but the data would be in both
'Sheet 1'!F2:F100 AND in 'Sheet 2'!F2:F100
Like this:
=LET(dataArray, VSTACK('Sheet 1'!A2:F100, 'Sheet 2'!A2:F100),
criteriaArray, VSTACK('Sheet 1'!F2:F100, 'Sheet 2'!F2:F100),
FILTER(dataArray, (criteriaArray>=DATE(2024, 10, 1))*(criteriaArray<=DATE(2024, 12, 31)), "No Match"))Amazing, thanks! I googled and asked CHAT GPT but you make it much easier!