Jun 03 2022 01:15 PM - edited Jun 03 2022 02:39 PM
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)
Jun 03 2022 01:29 PM
Jun 03 2022 01:35 PM - edited Jun 03 2022 01:36 PM
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.
Jun 03 2022 01:57 PM
@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)
Jun 03 2022 02:38 PM
Jun 03 2022 02:43 PM - edited Jun 03 2022 02:49 PM
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
Jun 03 2022 05:10 PM
@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.
Jun 03 2022 05:37 PM
SolutionJun 03 2022 06:02 PM
Jun 03 2022 06:32 PM
Jun 03 2022 05:37 PM
Solution