Extracting a figure from one cell base upon information from 2 other cells

**jono7gold** · 02-20-2014, 11:37 AM

Hi

I need a formula where I can look at information in 2 different cells and pick a figure from another cell linked to those 2 other cells.

For example - Cell A1 will be text - "System 1", Cell B1 will have text "WC 24/02/14", Cell C1 will have a currency figure "£1000".

How can I show cell C1 in another cell if cells A1 & B1 are matched to what I enter in the formula??

Cheers

Jon

**p24leclerc** · 02-20-2014, 09:20 PM

suppose you have a table in columns A, B and C with some values in it as described in your thread.
Now suppose you have in cell I2 a value naming a system and in J2 a value corresponding to a WC... thing.
Now, in K2 this formula will bring in the proper corresponding value to those 2 inputs.

Please Login or Register  to view this content.

It will only work there are only numbers in column C range.

the following formula will work will letters too:

Please Login or Register  to view this content.

**Tony Valko** · 02-20-2014, 09:29 PM

Originally Posted by p24leclerc

=SUMPRODUCT((A1:A13=I2)*(B1:B13=J2)*C1:C13)

It will only work there are only numbers in column C range.

If you use the comma separator syntax it will ignore text entries in the sum range:

=SUMPRODUCT(--(A1:A13=I2),--(B1:B13=J2),C1:C13)

Or, since they're using Excel 2010 they could use the SUMIFS function:

=SUMIFS(C1:C13,A1:A13,I2,B1:B13,J2)