Subtract like values that share similar data

[rrbailey]

I have 4 sets of data that I am attempting to compare and subtract the numbers in the columns to the right of said data. Column A and Column D are the similar static data. Colmn B and E are the variable data. I have tried the following formula but it only seems to compare left to right on the rows as opposed to searching the entire ranges and finding and subtracting the right numbers. =IF(A3:A132=D3:D56,B3-E3,0)

[#2]

That is correct, that formula will go from A3 to A132 and increment D3 to D132 accordingly and perform each calculation on those individual values. It sounds like you want to use a SUMIFS but I'm not sure of a few things: Is this formula to create a column of data in another column like F? and if you have the same value more than 1x in column A do you want to subtract all corresponding values in D? Or are all values in A unique and all values in D unique but just not in the same order and you want an easy way to do a diff of like values? What about values in D that aren't in A?

[#3]

Apologies for not being very clear.

Column A has more values in it then D. Which causes it to be out of order of what is in D. However the values will not change and there is only 1 instance of that value in both columns however A will have sum values that are not present in D. So I am attempting to take the numerical values that are next to those coulmns and search and subtract E from B. Also yes the formula will be entered into another coulmn, for example F.

[#4]

@rrbailey  If you have Excel 365 try this:

=B1:B100 - XLOOKUP(A1:A100,D1:D100,E1:E100,0)

if you have an older version of Excel something like this:

=B1 - IFERROR(VLOOKUP(A1,D$1:E$100,2,0),0)

[#5]

I am using 2016 so the first one simply returned ?NAME

and the second one did not work because row 1 is actually merged cells with no data. I attempted to change it like =B:B and A:A but it again only subtracted left to right row by row

I have attached a picture that hopefully makes the data and format more clear

[#6]

no picture. (edit: I see it now you put it in the original post)
if row 1 doesn't have data then start on row 2 (edit: I see you start on row 3). but if you don't have Excel 365 or at least Excel 2021 you can not use dynamic ranges so you can only enter a range like A:A inside a function that allows ranges. you will need to 'Fill Down' the formula to all the rows you need. So starting with F3:
=B3 - IFERROR(VLOOKUP(A3,D$3:E$100,2,0),0)
and fill down
This formula is assuming the data is in row 3 to 100 if more or less then change the range D$3:D$100 accordingly

edit: also, you might want to consider putting this formula in column C as it corresponds to the values in column A/B and being in column F might be confusing with D 

OR maybe make column F: =A3 and put the above formula in column G 

[#7]

@mtarler Appreciate your assistance on this, however it still seems as if the formula is doing some weird stuff, for example the result of B5 - E4 is in F5 when it should be in F4.

[#8 · Accepted solution]

so you only want the values in columns D/E to have the difference shown? Try this:
=IFERROR(VLOOKUP(D3,A$3:B$100,2,0),0)-E3
So if it doesn't find the name in col D in col A then it will return 0 - D3 or basically negative of the value in E3

[#9]

Thank you this works perfectly! How could it be adapted to instead of reflecting a negative value of whats in column E and instead show N/A or 0?

[#10]

=IFERROR(VLOOKUP(D3,A$3:B$100,2,0)-E3,"N/A")
or replace that "N/A" with 0 or blank ("")