Forum Discussion
Solved
Fetch data depending on value in list
I have a list of lines with a number of cells each containing values referring to the first cell , i.e.: Sweater, 10, USD Car, 10000, USD Socks, 2, USD In the last cell of every line I've made...
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")
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"))