Forum Discussion
Solved
Subtract like values that share similar data
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 varia...
- 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
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?
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.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)- 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
