Vlookup one value then another
 

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

Vlookup one value then another
 

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
...
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

Vlookup one value then another
 

=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

Vlookup one value then another
 

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

"Michelle7890" wrote in message
...
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

Vlookup one value then another
 

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
...
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

Vlookup one value then another
 

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

Vlookup one value then another
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Michelle7890" wrote in message
...
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
...
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

Vlookup one value then another
 

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

"Don Guillett" wrote in message
...
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

"Michelle7890" wrote in message
...
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