Forum Discussion
Subtract like values that share similar data
- 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
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 clearno 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 accordinglyedit: 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
