Search for a cell value based on 2 references

[IN_FOR_SIN]

Hi

I have a sheet (sheet 1) of financial data for multiple buildings as follows:

Building Budget Type Budget £

Centre A Employees £1
Centre A Premises £2
Centre A Supplies £3
Centre A Income £4
Centre B Employees £5
Centre B Premises £6
Centre B Supplies £7
Centre B Income £8
Centre C Employees £9
Centre C Premises £10
Centre C Supplies £11
Centre C Income £12
etc, etc, etc

As you can see, column A is in blocks (4 lines of the same name) and column B has repetitve lines over 4 lines.

From another sheet (sheet 2), I wish to be able to specify a buidling (Centre B) and budget type (Supplies) and return the value in budget £ (£7)

I was considering using VLOOKUP but the data is not sorted alphabetically and I really need to find a way to keep it in its original list.

Any help would be much appreicated, thanks in advance

[NBVC]

if you are using XL2007 or later, try SUMIFS

e.g

=SUMIFS(Sheet1!$C$2:$C$20,Sheet1!$A$2:$A$20,A2,Sheet1!$B$2:$B$20,B2)

where database is in Sheet1!A2:C20 and lookup values are in A2 and B2 for Building and Budget Type, respectively.

Adjust ranges to suit.

if using 2003 or earlier:

=INDEX(Sheet1!$C$2:$C$100,MATCH(1,INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$B$2:$B$20=B2),0),0))

[ramananhrm]

You can try this solution as well

Pleas use it with Ctrl+Shift+Enter
=INDEX(Sheet1!$C$1:$C$12,MATCH(A1&B1,Sheet1!$A$1:$A$12&Sheet1!$B$1:$B$12,0))

[IN_FOR_SIN]

These are great, thanks

In some cells I return the error "#N/A" because that specific line doesn't exist. Is there a way of returning a value of "0" in these instances?

Thanks again

[IN_FOR_SIN]

I am using Excel 2003 by the way, so I am utilising the INDEX formula.

[NBVC]

tRY:

=LOOKUP(9.999999E+307,CHOOSE({1,2},0,INDEX(Sheet1!$C$2:$C$20,MATCH(1,INDEX((Sheet1!$A$2:$A$20=A2)*(Sheet1!$B$2:$B$20=B2),0),0))))

[IN_FOR_SIN]

NBVC - you are a genius!!!

Thank you so much