IF Functions, VLOOKUP?

**s.tara91** · 09-05-2013, 11:30 PM

Hi All!

I have attached a copy of the spreadsheet i am working on. I need a formula that works as below to go into the difference column.

Basically I need a formula to look at the two staff number columns and then if they are the same to look at the two FTE columns, if these are the same then the difference will be 0 if there are changes I would like to know what the change was whether a positive or a negative depending if the FTE is lower or higher than the previous month. In some cases staff numbers in the first staff number column A will not be in the staff number column C which is okay i just need to know once again the difference in the FTE.

If a staff number in column A is no longer in staff number column C then the employee has probably resigned so if their FTE was 1 the difference will be -1.
If a staff number in column C is now there and is not in column A then the employee is probably new so if their FTE is 1 the difference will be +1.

I hope this makes sense!

Help!

Thanks

FTE Formula Workbook.xlsx

**FDibbins** · 09-05-2013, 11:43 PM

Not sure I fully understand what you want, but give this a try in E4, copied down...
=D4-IFERROR(VLOOKUP(C4,$A$4:$B$1670,2,0),1)

**s.tara91** · 09-06-2013, 12:08 AM

Hiya,

I think this mostly works but there were a couple of things i hope i have explained well enough?

It does'nt work if there is nothing in cell B and there is something in cell D. If a new number appears in cell C that wasnt in cell A the FTE in cell D needs to appear in the difference.

Also if there is information in the columns A & B and nothing in the columns C & D but the information in columns A & B has been picked up formula wise in a line number earlier then it is being picked up in the difference column as a negative but should be as it has been picked up prior. For example if you copied cells C1685:D1685 and pasted into cell A1689 the difference is -1 but this difference has already been picked up from cell A1667 so we have already recorded the was 0 difference between B & D and don't want to repeat that score but its possible this could occur.

**s.tara91** · 09-06-2013, 12:11 AM

It is possible for the A & B columns to have a longer listing of information in the cells than C & D columns so this is when that second issue would occur.

Hope i make sense! Thanks for the help!