How To match two cells and display data...

[coccio]

Code account data
3AA00 1100000 33.00
3AB00 1100000 5.00
3AE20 1100000 8.00
3AA00 1101000 55.00
3AB00 1101000 434.00


3AA00 1100000

I'm not quite sure how this is done maybe functions like VLOOKUP, INDEX and MATCH

I want it to look through excel find the code such as 3AA00 and then look for the account in the spreadsheet such as 1100000 and if one matches and not the other to make sure it continues searching until it finds those two matches and displays the data which in this case is 33.00

[Aladin Akyurek]

On 2002-03-20 06:56, coccio wrote:
Code account data
3AA00 1100000 33.00
3AB00 1100000 5.00
3AE20 1100000 8.00
3AA00 1101000 55.00
3AB00 1101000 434.00


3AA00 1100000

I'm not quite sure how this is done maybe functions like VLOOKUP, INDEX and MATCH

I want it to look through excel find the code such as 3AA00 and then look for the account in the spreadsheet such as 1100000 and if one matches and not the other to make sure it continues searching until it finds those two matches and displays the data which in this case is 33.00

Lets say that A1:C6 houses the sample data you provided, D2 the lookup value/condition 3AA00, and E2 the lookup value/condition 1100000.

=IF(SUMPRODUCT(ISNUMBER(MATCH(D2&E2,$A$2:$A$6&$B$2:$B$6,0))+0),INDEX($C$2:$C$6,SUMPRODUCT(MATCH(D2&E2,$A$2:$A$6&$B$2:$B$6,0))),"")

will retrieve the desired value.

Note. If you intend to use the above formula in lots of cells, it will incure some performance costs.

Aladin