Automate Index / Match forumla - add an IF statement? or another Match??

Ann Foley

Hi,

I have a report that I will be data dumping into monthly (1st pic called "Jan GSS"). I created an Index/Match formula (in second pic) to pull those numbers over into my main sheet (GSS ITRec column):

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))

How can I make this formula better so that when I add the Feb and March and so on data to the sheet "Jan GSS" it will update the approprate month rows in main sheet for each property (pic 2)?

That way I can apply the formula to all the cells and don't have to update it monthly. Thank you!!

Genevieve P.

Hi Ann,

Based on how you have your first sheet set up (Jan GSS), you would need to update the column reference for each month and create an individual formula per-column in the source sheet & month in your destination sheet, like so:

January

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))

February

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Feb Column GSS MARSHA}, 0))

March

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {March Column GSS MARSHA}, 0))

... etc

You would only need 12 of these (since I presume your Property 2 has a different Marsha@row than Property 1). Additionally, you can set these up now, so when you have new data you won't need to remember to add it in.

Since there is nothing to currently match in these months, you'll receive a NOMATCH error. To eliminate this, you can add an IFERROR function around your current formula, like so:

=IFERROR(INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0)), "Not Started")

I have this saying "Not Started" but you could keep it blank by using "" instead.

Let me know if you have any questions about this!

Cheers,

Genevieve

Genevieve P.

Hi Ann,

Based on how you have your first sheet set up (Jan GSS), you would need to update the column reference for each month and create an individual formula per-column in the source sheet & month in your destination sheet, like so:

January

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0))

February

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Feb Column GSS MARSHA}, 0))

March

=INDEX({Jan GSS ITR}, MATCH(Marsha@row, {March Column GSS MARSHA}, 0))

... etc

You would only need 12 of these (since I presume your Property 2 has a different Marsha@row than Property 1). Additionally, you can set these up now, so when you have new data you won't need to remember to add it in.

Since there is nothing to currently match in these months, you'll receive a NOMATCH error. To eliminate this, you can add an IFERROR function around your current formula, like so:

=IFERROR(INDEX({Jan GSS ITR}, MATCH(Marsha@row, {Jan GSS MARSHA}, 0)), "Not Started")

I have this saying "Not Started" but you could keep it blank by using "" instead.

Let me know if you have any questions about this!

Cheers,

Genevieve

Ann Foley

Thank you!