I need a look-up function within Excel that works on an inconsistent set of data

[Carlos 08]

Any idea's? Basically, I get fuel costs each month on a spreadsheet for each team within our company. It has the months scrolling across the top (B2:L1) and the team names going down (A1 onwards) with a total under each one team. The problem is, we get new people joining the company or leaving each month, this causes the data to shift up and down the rows, so the totals for each team aren't in the same place each time I receive the sheet. I need to extract each teams total each month. Is there an offset and match or look up function that would help?

I need it to look at 'Team' and extract the 'Total' underneath corresponding to the month in question.

I hope you can help, I'm stuck! Cheers guys

I'll give you an example

Team Name on A5
January's Total B8, Feb's Total C8

But the following month the totals could be on row 11 and not 8, so it shifts. We can't alter the spreadsheet to have fixed cells, we aren't allowed! I need to get the totals for each team.

Thanks

 

[Fazza]

Welcome, Carlos.

It sounds like Excel's INDEX function will be suitable. Help refers.

Combined with MATCH functions it can determine which row or column to return. So, something like below. HTH, Fazza

=INDEX(A1:L1000,MATCH(team name,A1:A1000,0),MATCH(month,A1:L1,0))

 

[Carlos 08]

Many thanks for that, I hadn't thought about that method. I'll give it a go and let you know