Excel 2007 : Lookup of 2 columns of data

[bjamin_w]

Hi - I want to perform a lookup of two columns of data. ie if both colums match their test data, then return the value in the third colum of the lookup table.

what is the best formula to use?

many thanks

[DonkeyOte]

All depends on

a) context

b) data types

Are there multiple instances of the combination ?

Are the values being returned numeric ?

[bjamin_w]

Its looking up text.

Column A = Supplier (text)
Column B = Brand (text)
Column C = turnover (value)

i have several tables of the above data for different seasons.

so if Colum A= ColumnA in the look up table and column B = Column B in the lookup table it returns Cloumn C in the lookup table.

[bjamin_w]

and no there wont be multiple instances of the combination

[DonkeyOte]

Given

a) output is numeric

b) use of XL2007

it would make sense to utilise SUMIFS function

=SUMIFS(table!$C$1:$C$1000,table!$A$1:$A$1000,$A1,table!$B$1:$B$1000,$B$1)

where table is sheet containing raw data, A1 & B1 contain Supplier & Brand of interest.

If you're conducting a matrix based output (ie multiple brand / supplier combinations) then you would be best served using a Pivot Table - for a one off calc (or handful thereof) the above is fine.

(note SUMIFS is not backwards compatible with versions of Excel pre XL2007)

[bjamin_w]

thanks ill give that a try

[bjamin_w]

thats works perfectly, thanks