IF / THEN with Formula - Multiple Columns / Rows

[Philby]

Looking for assistance in what may be a simple formula, but can't work it out.

I have a Column with numbers and an adjacent Column with associated dollar values and am looking to compare the same numbers from another Column and then calcualte if there is a variance in the dollar values and return the variance as a dollar value.

The attached file shows the basic data, with the individual formulae of what I am trying to achieve, but looking to see if there is a formula that can be applied to the 'variance' Column.

As more Columns are added with more data, then the next 'Variance' will be a formula based on the previous Columns.

Example is in the attached file, but looks like this: =IF(A5=D7,E7-B5) I'm looking to apply this to a whole Column to search for the corresponding / matching number values and then if found, to calculate the variance with the formula specific to the associated cells.

Thanks in advance.
Philby

 

[p45cal]

Something like, in cell F5:

=IFERROR(E5-INDEX($B$5:$B$500,MATCH(D5,$A$5:$A$500,0)),"not applicable")

copied down.

 

[Philby]

Something like, in cell F5:

=IFERROR(E5-INDEX($B$5:$B$500,MATCH(D5,$A$5:$A$500,0)),"not applicable")

copied down.

Thank you for this - (obviously) it worked just as I wanted, and saved me a LOT of grief - I've learnt a lot just from understanding how this formula applies. Thanks again for your response and help.

 

[bosco_yip]

And,

you can use VLOOKUP function instead of INDEX+MATCH in your example.

In cell F5, formula copied down:

=IFERROR(E5-VLOOKUP(D5,A:B,2,0),"not applicable")

Or,

=IFERROR(E5-VLOOKUP(D5,A:B,2,0),"")

Regards