Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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 "L. Young" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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 "Eduardo" wrote: 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 "L. Young" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
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 --------------- Jacob Skaria "L. Young" wrote: 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 "Eduardo" wrote: 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 "L. Young" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
Perfect. Thank you.
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "L. Young" wrote: 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 "Eduardo" wrote: 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 "L. Young" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |