I have a spreadsheet (example below). I want to be able to look up the first value which is a cost center, then within that cost center find an account number and return the $value. For example I want to look up account 12001 in Cost Center 300 and get the value answer $10. How can I do this? Column A Column B Column C Cost center # Account# $Value 300 12001 $10 300 12002 $30 300 12003 $50 400 12001 $20 400 12002 $40 400 12003 $60
One way: E1 = lookup cost center = 300 F1 = lookup account number = 12001 =SUMPRODUCT(--(A2:A10=E1),--(B2:B10=F1),C2:C10) -- Biff Microsoft Excel MVP "Michelle7890" wrote in message news:0DB47098-A667-4A14-BD8A-E4BFFDE06387@microsoft.com... >I have a spreadsheet (example below). I want to be able to look up the >first > value which is a cost center, then within that cost center find an account > number and return the $value. For example I want to look up account 12001 > in > Cost Center 300 and get the value answer $10. > How can I do this? > > Column A Column B Column C > Cost center # Account# $Value > 300 12001 $10 > 300 12002 $30 > 300 12003 $50 > 400 12001 $20 > 400 12002 $40 > 400 12003 $60
=SUMPRODUCT(--(A2:A100=costcenter),--(B2:B100=account),(C2:C100)) Hope this helps. -- John C "Michelle7890" wrote: > I have a spreadsheet (example below). I want to be able to look up the first > value which is a cost center, then within that cost center find an account > number and return the $value. For example I want to look up account 12001 in > Cost Center 300 and get the value answer $10. > How can I do this? > > Column A Column B Column C > Cost center # Account# $Value > 300 12001 $10 > 300 12002 $30 > 300 12003 $50 > 400 12001 $20 > 400 12002 $40 > 400 12003 $60
One way to lookup 400 for acct 12002 to get 40 =VLOOKUP(12002,INDIRECT("b"&MATCH(400,A:A,0)&":c"&MATCH(400,A:A)),2) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Michelle7890" wrote in message news:0DB47098-A667-4A14-BD8A-E4BFFDE06387@microsoft.com... >I have a spreadsheet (example below). I want to be able to look up the >first > value which is a cost center, then within that cost center find an account > number and return the $value. For example I want to look up account 12001 > in > Cost Center 300 and get the value answer $10. > How can I do this? > > Column A Column B Column C > Cost center # Account# $Value > 300 12001 $10 > 300 12002 $30 > 300 12003 $50 > 400 12001 $20 > 400 12002 $40 > 400 12003 $60
Thank you "T. Valko" wrote: > One way: > > E1 = lookup cost center = 300 > F1 = lookup account number = 12001 > > =SUMPRODUCT(--(A2:A10=E1),--(B2:B10=F1),C2:C10) > > -- > Biff > Microsoft Excel MVP > > > "Michelle7890" wrote in message > news:0DB47098-A667-4A14-BD8A-E4BFFDE06387@microsoft.com... > >I have a spreadsheet (example below). I want to be able to look up the > >first > > value which is a cost center, then within that cost center find an account > > number and return the $value. For example I want to look up account 12001 > > in > > Cost Center 300 and get the value answer $10. > > How can I do this? > > > > Column A Column B Column C > > Cost center # Account# $Value > > 300 12001 $10 > > 300 12002 $30 > > 300 12003 $50 > > 400 12001 $20 > > 400 12002 $40 > > 400 12003 $60 > > >
Thank you! "John C" wrote: > =SUMPRODUCT(--(A2:A100=costcenter),--(B2:B100=account),(C2:C100)) > > Hope this helps. > -- > John C > > > "Michelle7890" wrote: > > > I have a spreadsheet (example below). I want to be able to look up the first > > value which is a cost center, then within that cost center find an account > > number and return the $value. For example I want to look up account 12001 in > > Cost Center 300 and get the value answer $10. > > How can I do this? > > > > Column A Column B Column C > > Cost center # Account# $Value > > 300 12001 $10 > > 300 12002 $30 > > 300 12003 $50 > > 400 12001 $20 > > 400 12002 $40 > > 400 12003 $60
You're welcome! -- Biff Microsoft Excel MVP "Michelle7890" wrote in message news:38B82F40-437C-4518-8FD8-F0FF4F217371@microsoft.com... > Thank you > > "T. Valko" wrote: > >> One way: >> >> E1 = lookup cost center = 300 >> F1 = lookup account number = 12001 >> >> =SUMPRODUCT(--(A2:A10=E1),--(B2:B10=F1),C2:C10) >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "Michelle7890" wrote in message >> news:0DB47098-A667-4A14-BD8A-E4BFFDE06387@microsoft.com... >> >I have a spreadsheet (example below). I want to be able to look up the >> >first >> > value which is a cost center, then within that cost center find an >> > account >> > number and return the $value. For example I want to look up account >> > 12001 >> > in >> > Cost Center 300 and get the value answer $10. >> > How can I do this? >> > >> > Column A Column B Column C >> > Cost center # Account# $Value >> > 300 12001 $10 >> > 300 12002 $30 >> > 300 12003 $50 >> > 400 12001 $20 >> > 400 12002 $40 >> > 400 12003 $60 >> >> >>
Hopefully you won't have more than one double match in each block or the others won't work. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don Guillett" wrote in message news:eyjoMbWKJHA.3756@TK2MSFTNGP05.phx.gbl... > One way to lookup 400 for acct 12002 to get 40 > =VLOOKUP(12002,INDIRECT("b"&MATCH(400,A:A,0)&":c"&MATCH(400,A:A)),2) > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett1@austin.rr.com > "Michelle7890" wrote in message > news:0DB47098-A667-4A14-BD8A-E4BFFDE06387@microsoft.com... >>I have a spreadsheet (example below). I want to be able to look up the >>first >> value which is a cost center, then within that cost center find an >> account >> number and return the $value. For example I want to look up account >> 12001 in >> Cost Center 300 and get the value answer $10. >> How can I do this? >> >> Column A Column B Column C >> Cost center # Account# $Value >> 300 12001 $10 >> 300 12002 $30 >> 300 12003 $50 >> 400 12001 $20 >> 400 12002 $40 >> 400 12003 $60 >
