Multiple Criterion to Display Value from 2 sheets

Elaine Perrie

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!

Gillian C

Hi @Elaine Perrie

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 :)

Gillian C

Hi @Elaine Perrie

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 :)

Elaine Perrie

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!