Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with Multiple criteria and multiple sheets
Hi
I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with Multiple criteria and multiple sheets
or
=SUMPRODUCT(--(Sheet1!$A$2:$A$23=Sheet2!A2),--(Sheet1!$B$2:$B$23=Sheet2!$B$2)*(Sheet1!$H$2:$H$23 )) Both formulas on sheet2 Peter "Cinny" wrote: Hi I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with Multiple criteria and multiple sheets
Cinny
Try this =SUMPRODUCT(--(Sheet1!$A$2:$A$23=Sheet1!A2),--(Sheet1!B2:B23=Sheet1!B2)*(Sheet1!$H$2:$H$23)) Regards Peter "Cinny" wrote: Hi I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with Multiple criteria and multiple sheets
Cinny
Insert a column in A to produce a unique reference. In A2 type =B2&C2 and copy down. Sort the list on reference and use VLOOKUP in the normal way. Your budget will be in column 9 (I) Peter "Cinny" wrote: Hi I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with Multiple criteria and multiple sheets
Hi Billy
thanks for the inforamtion, unfortunately, I can not add the two cells together because I later on need need to generate an interactive report on the unique identifier, in addition, the sum product doesn't seem to be right for me, the result from Sheet 2, column c needs to be in Sheet 1 column h, which means the formula needs to be entered i assume in sheet 1 column h? Am I misunderstanding? Thanks C "Billy Liddel" wrote: Cinny Insert a column in A to produce a unique reference. In A2 type =B2&C2 and copy down. Sort the list on reference and use VLOOKUP in the normal way. Your budget will be in column 9 (I) Peter "Cinny" wrote: Hi I have a worksheet with a summary page like Column A Customer name Column B Industry and in colum H I want to return a budget figure based on the two criteria of Column A & B. The budget figure is in another sheet where column A & B once again have the customer name and Industry and the budget figure is in column C. How do I get the figure from the second sheet in column C using the multiple criteria into the first sheet? hope this makes sense. Cinny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria over multiple sheets | Excel Worksheet Functions | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Look up using multiple sheets and multiple criteria | Setting up and Configuration of Excel | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) |