Subtract value if value matches that of another cell

[jeffersdean]

Hello all, and thanks ahead of time for any help you are able to provide. New to what I would consider more complicated Excel formulas, so I'll try not to confuse with my request!

In the attachment, I'd like to populate column N on the September 2022 sheet with something like =M2-'August 2022'!M2 but only if the value in column B matches. The problem is, I need to do this month over month and the values in column B will not always abe in the same order. I believe it will need to match via VLOOKUP?? I hope this makes sense, please ask clarifying questions as needed.

Thank you!

[etaf]

just checking

you want to subtract the August value in N from the September value in N only for matching Keys

What if they do not match at all - what do you want in the cell

the values in N are not numbers but text
so i have just done a formula to remove the $ - and put that into N

Now i have a formula in O
so
=N2-INDEX('August 2022'!N:N,MATCH('September 2022'!B2,'August 2022'!B:B,0))
will subtract

If there is no key match you get N/A error - which with an IFERROR can be fixed

There maybe a way to automate the months info
so it goes to the correct sheet based on the month - but need to see if you need that

anyway heres a start

[Sam Capricci]

this should work for you in the sept 2022 tab in cell N2 dragged down... =IFERROR(M2-VLOOKUP(B2,'August 2022'!$B$2:$M$400,12,FALSE),"")
I believe you also have access to xlookup which might work as well.

[Sam Capricci]

What Wayne noted about text and not numbers is interesting, my formula returned a result as you can see in the attached.

[etaf]

maybe its something on my excel - but its changing the value to text $12345
if i then put in N
M2-1 - i get a value error

the OP has the formula and so if its is a number then they will both work - if not then the text needs to be changed to a number

heres your file back
still gives an error

as i say maybe its my excel converting

[Sam Capricci]

Wayne, it is interesting because you are right excel isn't recognizing them as numbers as =isnumber(M2) in the august tab returns FALSE and that is on mine yet the formula is working.

[TMS]

Try:

Please Login or Register  to view this content.

[jeffersdean]

Thank you all so much! Will definitely report back results when back at desk.

[TMS]

Looks like manually entered dollar signs rather than numeric values with appropriate formatting.

[jeffersdean]

just checking

you want to subtract the August value in N from the September value in N only for matching Keys

What if they do not match at all - what do you want in the cell

the values in N are not numbers but text
so i have just done a formula to remove the $ - and put that into N

Now i have a formula in O
so
=N2-INDEX('August 2022'!N:N,MATCH('September 2022'!B2,'August 2022'!B:B,0))
will subtract

If there is no key match you get N/A error - which with an IFERROR can be fixed

There maybe a way to automate the months info
so it goes to the correct sheet based on the month - but need to see if you need that

anyway heres a start

You guys are awesome, thanks for all the help. I ended up using this version with an IFERROR after converting M to a number and removing the dollar signs. The formula ended up being
=IFERROR(M7-INDEX('August 2022'!M:M,MATCH('September 2022'!B7,'August 2022'!B:B,0)),"not found").

I'm curious about automating the months. It would also be subtracting column M from the previous month, which would be on the sheet prior as well.

Thanks again.

[etaf]

I'm curious about automating the months.

can you give examples of this

=IFERROR(M7-INDEX('August 2022'!M:M,MATCH('September 2022'!B7,'August 2022'!B:B,0)),"not found").

you maybe able to use an indirect() function to do some automated dates
as you add more sheets