Using Vlookup or SUMIF to perform subtraction based on field name

Hi, On the attached document, I need to find a formula for cells S9-V26 that will allow me to determine the difference between data in columns W-Z and rows 29-31 for matching team names/IDs. The formulas in there at the moment assume that all teams names will always appear in the correct order in rows 29-31 but this is not always the case as this data is pasted from another database where null values are not stored and therefore some teams may not appear (e.g. in the attached example there is no data for team 12).

I have removed all names for security. Normally the names are a combination of letters,numbers and characters and these change from month to month so I usually use the team ID in column A which is fixed and does not change.

Can i use something like VLOOKUP as I have used in columns J-M to get around this?

Any help greatly appreciated

I also work in a call centre and am familiar with the sort of thing that you are trying to do.

VLookup is causing a lot of problems with your data - if you look at C22 you are finding a sum of the data for Team 13 not Team 12. Cell K20 ( Team11) is reading data for Team 10 (C38). This means that the results data is open to query - not the most comforable position to be.

You would be better off using Index Match or SUMIF - SUMPRODUCT would also be possible - you can Google those and get some very good working examples.

edvwvw

Thanks edvwvw. i think the problem with the vlookup is because I hastly removed names and replaced them with anything so they didn't match up. My original report seems to work OK. I take your point though. I would much rather use some matching function. I have googled your suggestions but I just keep finding examples that show how to return one cell value. How do I return a calculation?

If I get some time today I will have another look

eddvwvw