Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 columns of data to return an answer (Vlookup???)
Here is an example of the spreadsheet I have:
Width Thick Cost 1 1 $20 1 2 $25 1 3 $30 2 1 $25 2 2 $30 3 3 $35 I want my salespeople to enter in the width in one cell and the thickness in another cell and have it return the cost. I imagine I will have to use the Vlookup function. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 columns of data to return an answer (Vlookup???)
On Sep 2, 2:14*pm, wrote:
Here is an example of the spreadsheet I have: Width * *Thick * *Cost 1 * * * * * 1 * * * * *$20 1 * * * * * 2 * * * * *$25 1 * * * * * 3 * * * * *$30 2 * * * * * 1 * * * * *$25 2 * * * * * 2 * * * * *$30 3 * * * * * 3 * * * * *$35 I want my salespeople to enter in the width in one cell and the thickness in another cell and have it return the cost. *I imagine I will have to use the Vlookup function. I need to add that I am looking for some help on which formula to use to achieve thisl. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 columns of data to return an answer (Vlookup???)
Assuming your salespeople put the Width value in D1 and the Thick value in
E1 (and assuming your data is in Columns A thru C), then this formula will return the Cost... =SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*C1:C1000) -- Rick (MVP - Excel) wrote in message ... Here is an example of the spreadsheet I have: Width Thick Cost 1 1 $20 1 2 $25 1 3 $30 2 1 $25 2 2 $30 3 3 $35 I want my salespeople to enter in the width in one cell and the thickness in another cell and have it return the cost. I imagine I will have to use the Vlookup function. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 columns of data to return an answer (Vlookup???)
VLOOKUP() isn't going to be able to do it for you. You have to be able to
examine both the width and thickness columns to get the right answer. So SUMPRODUCT() becomes your friend here. For the example below, your table is on 'Sheet1' and it goes from row 1 down to row 7, with row 1 being labels. It is in columns A, B and C. On the same sheet, or another your user puts the width into A1 and the Thickness into B1, and on that sheet you have this formula: =SUMPRODUCT(--('Sheet1'!$A$1:$A$7=$A$1),--('Sheet1'!$B$1:$B$7=$B$1),($C$1:$C$7)) " wrote: On Sep 2, 2:14 pm, wrote: Here is an example of the spreadsheet I have: Width Thick Cost 1 1 $20 1 2 $25 1 3 $30 2 1 $25 2 2 $30 3 3 $35 I want my salespeople to enter in the width in one cell and the thickness in another cell and have it return the cost. I imagine I will have to use the Vlookup function. I need to add that I am looking for some help on which formula to use to achieve thisl. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP - Return value of the cell below the formula's answer | Excel Discussion (Misc queries) | |||
VLookup: Return Multiple Columns? | Excel Discussion (Misc queries) | |||
vlookup to return 2 columns | Excel Discussion (Misc queries) | |||
vlookup 3 columns all return same | Excel Discussion (Misc queries) | |||
Can I return multiple columns from a vlookup? | Excel Worksheet Functions |