VLOOKUP

  • Thread starter Thread starter L. Young
  • Start date Start date
L

L. Young

I am wanting to have a nested VLOOKUP. I have customer name in one column
and part code in another column, then a selling price. I want to lookup the
selling price based on customer and part code. I know how to use VLOOKUP to
get the asnwer but it only uses one column as the look up and I need it based
on both the customer and the part code.

Please help.
 
Hi,
your information is in Column A, B and C and then in D1 you enter your
customer and in D2 your code and want to get the price in D3, so here enter

=sumproduct(--(D1=$A$1:$A$1000),(D2=$B$1:$B$1000),$C$1:$C$1000)

change range to fit your needs but remember to keep the same range in the
three parts of the formula
 
This will sum the answers, correct. I might have the same customer with same
item more than once and I only want the first answer it finds not the sum of
the answer. My speadsheet has sales by customer by item by invoice date, so
there is more than one answer to each, but I have it sorting in desending
order so the first answer is the most recent selling price.

Let me know your thoughts. Below is a sample

Customer Item Invoice Date Selling Price
CUSA 123 9/25/09 $1.50
CUSA 123 5/25/09 $1.25
CUSB 123 9/01/09 $1.75
 
Please note that this is an array formula. you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Customer Item Invoice Date Selling Price
CUSA 123 9/25/09 $1.50
CUSA 123 5/25/09 $1.25
CUSB 123 9/01/09 $1.75

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="CUSA")*($B$2:$B$10=123),0))

If this post helps click Yes
 
Perfect. Thank you.

Jacob Skaria said:
Please note that this is an array formula. you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Customer Item Invoice Date Selling Price
CUSA 123 9/25/09 $1.50
CUSA 123 5/25/09 $1.25
CUSB 123 9/01/09 $1.75

=INDEX($D$2:$D$10,MATCH(1,($A$2:$A$10="CUSA")*($B$2:$B$10=123),0))

If this post helps click Yes
Click to expand...
 
Back
Top