Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
My first question is I want to lookup multiple values in the worksheet. In my
specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
For your first question try this array formula** :
=INDEX(D1:D12,MATCH(1,(A1:A12=E1)*(B1:B12=F1)*(C1: C12=G1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Not sure I understand your second question. -- Biff Microsoft Excel MVP "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
=SUMPRODUCT(--(A1:A12=E1),--(B1:B12=F1),--(C1:C12=G1),D1:D12)
This assumes that each record (row) is unique best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
Part 2:
Click on E1 Use Data | Data Validation In the dialog, specify List and use A1:A12 are the Source Similar for other two cells best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
Your formulas for lookup not working.
For data validation, I have no problem in E1. F1 is linked with E1 in the sense that one type have two manufacturers and others have three or four with diferent brands. And finally I in G1, I want the matched capacity of type and manufacturer, which are all different, like york chillers are in capacities of 2000, 3000, 4000 while carrier chillers are of 2050, 3015,4060. I want all these data validation are interlinked with each other. "Bernard Liengme" wrote: Part 2: Click on E1 Use Data | Data Validation In the dialog, specify List and use A1:A12 are the Source Similar for other two cells best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
OK for question 1.
In cell E1 I just did list through data validation the types of chillers like gas fired, electric and steam. In F1, I want to retrieve such a list in which when I select gas fired, it give to me the names of gasfired manufacturers and like wise. And in G1, I want just the capacities of both matched cell (E1 and F1) as the capacities of every manufacturer and type is different or for some types such capacities are not available, therefore I don't want to see those names in the list. "T. Valko" wrote: For your first question try this array formula** : =INDEX(D1:D12,MATCH(1,(A1:A12=E1)*(B1:B12=F1)*(C1: C12=G1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Not sure I understand your second question. -- Biff Microsoft Excel MVP "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
Bernard, I found the answer of my first question so please limited to the
second one. "Bernard Liengme" wrote: Part 2: Click on E1 Use Data | Data Validation In the dialog, specify List and use A1:A12 are the Source Similar for other two cells best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Multiple Values
Sounds like you want dependent drop downs. See this:
http://contextures.com/xlDataVal02.html -- Biff Microsoft Excel MVP "Zubair" wrote in message ... OK for question 1. In cell E1 I just did list through data validation the types of chillers like gas fired, electric and steam. In F1, I want to retrieve such a list in which when I select gas fired, it give to me the names of gasfired manufacturers and like wise. And in G1, I want just the capacities of both matched cell (E1 and F1) as the capacities of every manufacturer and type is different or for some types such capacities are not available, therefore I don't want to see those names in the list. "T. Valko" wrote: For your first question try this array formula** : =INDEX(D1:D12,MATCH(1,(A1:A12=E1)*(B1:B12=F1)*(C1: C12=G1),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Not sure I understand your second question. -- Biff Microsoft Excel MVP "Zubair" wrote in message ... My first question is I want to lookup multiple values in the worksheet. In my specific problem, I want to lookup three and four cells to find their matched resultant cell. For three lookups: I have in one cells type of products, in second the manufacturers of that products and in the last their capacities. How can I get the product dimension. A B C D Type Manufacturer Capacities Dimensions 1 Steam Fired York 1000 2 Steam Fired York 2000 3 Steam Fired Carrier 1000 4 Steam Fired Carrier 2000 5 Steam Fired Trane 1000 6 Steam Fired Trane 2000 7 Electrical Carrier 2000 8 Electrical York 2000 9 Electrical Carrier 1000 10 Electrical Carrier 2000 11 Electrical Trane 1000 12 Electrical Trane 2000 I have used INDEX formula nested with MATCH as: =INDEX(A1:D12,(MATCH(E1,A1:A12,0)*MATCH(F1,B1:B12, 0)*MATCH(G1,C1:C12,0)),4) This formula give to me right answer till 4 rows, after that either it gives random values or error. My second question is I want make list through validation in cells E1, F1 and G1 in such a manner that when I select from the list one of the types, it give me the manufacturers of selected type of equipments. After selection of both type and manufacturer, it gives their matched capacities as every manufacturer and type have different capacities. Please help me to find the solutions, Regards, Zubair |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Values Lookup | Excel Discussion (Misc queries) | |||
Multiple lookup values and adding multiple rates across together | Excel Worksheet Functions | |||
LOOKUP MULTIPLE VALUES | Excel Discussion (Misc queries) | |||
Lookup on multiple values | Excel Discussion (Misc queries) | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions |