Multiple Criterion to Display Value from 2 sheets
Hi! This is my first time asking some question here
I'm having trouble on coming up with a formula for what I want to do.
I have two sheets.
1st sheet contains MONTH column, YEAR Column and INVOICE VALUE Column
2nd sheet is where I will be putting the INVOICE VALUE to each month of the year.
They both have Row ID so that I can only gather values IF Row ID between the two sheets are the same.
What I'm trying to do is have multiple Criterion.
For example,
Row IDs between 2 sheets should be the same
Only collect value if Month = January and if Year = 2023
I know it has something to do with Index Collect but I can't seem to perfect the formula
I've tried the formula for "2023 January" Column
=INDEX(COLLECT({1st Sheet Range 1
Invoice Value
}, {1st Sheet Range 2
Month
}, "January", {1st Sheet Range 3
Year
}, "2023", MATCH([Row ID]@row, {1st Sheet Range 4
Row ID
}, 0)))
Below is the sample sheet for reference
1st Sheet:
2nd Sheet: to display invoice value from 1st sheet after meeting criterion
Thank you!
Best Answer
-
I think you were nearly there try:
=INDEX(COLLECT({1st Sheet Range 1 Invoice Value}, {1st Sheet Range 2 Month}, "January", {1st Sheet Range 3 Year}, "2023", {1st Sheet Range 4 Row ID}, [Row ID]@row),1)
Hope that helps :)
Answers
-
I think you were nearly there try:
=INDEX(COLLECT({1st Sheet Range 1 Invoice Value}, {1st Sheet Range 2 Month}, "January", {1st Sheet Range 3 Year}, "2023", {1st Sheet Range 4 Row ID}, [Row ID]@row),1)
Hope that helps :)
-
Hi @Gillian C
This helped! but what I changed was I turned the months into its numerical values, since for some reason, spelled out doesn't seem to really work, and your input that I shouldn't use Match since it could just be as simple as selecting the row ID! thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!