| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t
Hi
Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell A2:A100 and B2:B100 and return the text from C2:C100? The formula will be in sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2, A4:A100. Filtering and pivot tables won't do. Example sheet1: A: B: C: Job status Work-area Job status Running Office Some work left Stopped Workshop All done Running Warehouse Missing one item .... Result in sheet 2: A: B: C: Running Office: Warehouse: Workshop: Some work left Missing one item Maybe I need to use MATCH and INDEX? I also tried to combine two columns into one and then use VLOOKUP. Didn't work. Any help will be appreciated. -- Thanks John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t
=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!$A$2:$A$200=A1)* (Sheet1!$B$2:$B$200=B1),0))
this is an array formula, so ciomiit with Ctrl-Shift-Enter, not just Enter. -- __________________________________ HTH Bob "zzxxcc" wrote in message ... Hi Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell A2:A100 and B2:B100 and return the text from C2:C100? The formula will be in sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2, A4:A100. Filtering and pivot tables won't do. Example sheet1: A: B: C: Job status Work-area Job status Running Office Some work left Stopped Workshop All done Running Warehouse Missing one item ... Result in sheet 2: A: B: C: Running Office: Warehouse: Workshop: Some work left Missing one item Maybe I need to use MATCH and INDEX? I also tried to combine two columns into one and then use VLOOKUP. Didn't work. Any help will be appreciated. -- Thanks John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2 criteria lookup of text. Return text form column 3. SUMPRODU
Bob,
My mistake: The criteria No.1 is in sheet2, $A$1 and the rest in sheet2, A3:BJ3. (A3:C3 for this example, but the table actually extend beyond col.C). I adjusted accordingly, entered with ctrl+shift+enter , but still it didn't work. -- Thanks John "Bob Phillips" wrote: =INDEX(Sheet1!C:C,MATCH(1,(Sheet1!$A$2:$A$200=A1)* (Sheet1!$B$2:$B$200=B1),0)) this is an array formula, so ciomiit with Ctrl-Shift-Enter, not just Enter. -- __________________________________ HTH Bob "zzxxcc" wrote in message ... Hi Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell A2:A100 and B2:B100 and return the text from C2:C100? The formula will be in sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2, A4:A100. Filtering and pivot tables won't do. Example sheet1: A: B: C: Job status Work-area Job status Running Office Some work left Stopped Workshop All done Running Warehouse Missing one item ... Result in sheet 2: A: B: C: Running Office: Warehouse: Workshop: Some work left Missing one item Maybe I need to use MATCH and INDEX? I also tried to combine two columns into one and then use VLOOKUP. Didn't work. Any help will be appreciated. -- Thanks John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return text using Sumproduct | Excel Worksheet Functions | |||
| Quick Sumproduct vs Text return | Excel Discussion (Misc queries) | |||
| Sumproduct to return a text | Excel Worksheet Functions | |||
| LOOKUP text return text | Excel Worksheet Functions | |||
| lookup a text cell and return text | Excel Discussion (Misc queries) | |||
Linear Mode
