Vlookup one value then another

  • Thread starter Thread starter Michelle7890
  • Start date Start date
M

Michelle7890

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)
 
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)
 
Hopefully you won't have more than one double match in each block or the
others won't work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF multiple criteria 4
Double Lookup in grid 2
Calculating a Formula Subject to a Maximum and Minimum Value 2
Cost increase rate 3
sumif involving another column 4
Is there a way to use a multiple column array in SUMPRODUCT? 2
Return a value from one column using an array and a single row 1
Sumproduct excluding multiple criteria 8

Back
Top