| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array with multiple selection criteria
I have a 3 column list. Two columns have criteria which must be true in order
to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
If you have only a few values then use IF otherwise VLOOKUP...
Since you have two conditons... combine them (in the lookup range) in one column with A1&B1 the in VLOOKUP lookup on A1&B1 "Robert Robinson" wrote: I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Thank you for responding.
What is the separator for the two values? Comma, semicolon? -- Robert Robinson "Sheeloo" wrote: If you have only a few values then use IF otherwise VLOOKUP... Since you have two conditons... combine them (in the lookup range) in one column with A1&B1 the in VLOOKUP lookup on A1&B1 "Robert Robinson" wrote: I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
No need to have a separator... unless you want to have it for readability..
For example if Sheet 1 (the lookup range) has LastName in Col A, FirstName in Col B, and Age in Col C... Now if you want to find the AGE for LastName in Col A of Sheet2 and FirstName in Col B of Sheet2... Insert a Col before COl A of Sheet1 and enter the formula =A1&B1 and copy down... and in Sheet2 C1 enter =VLOOKUP(A1&B1, Sheet1!A:D,4,False) IF you have any separator in the formula for Col A of SHeet1 then you need to have the same in the VLOOKUP too... "Robert Robinson" wrote: Thank you for responding. What is the separator for the two values? Comma, semicolon? -- Robert Robinson "Sheeloo" wrote: If you have only a few values then use IF otherwise VLOOKUP... Since you have two conditons... combine them (in the lookup range) in one column with A1&B1 the in VLOOKUP lookup on A1&B1 "Robert Robinson" wrote: I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Sheet 2
Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 A6: =SUMPRODUCT(--(Sheet1!A1:A10="CF"),--(Sheet1!B1:B10="AMI"),Sheet1!C1:C10) A7: =SUMPRODUCT(--(Sheet1!A1:A10="CF"),--(Sheet1!B1:B10="BLV"),Sheet1!C1:C10) B6: =SUMPRODUCT(--(Sheet1!A1:A10="CI"),--(Sheet1!B1:B10="AMI"),Sheet1!C1:C10) B7: =SUMPRODUCT(--(Sheet1!A1:A10="CI"),--(Sheet1!B1:B10="BLV"),Sheet1!C1:C10) -- Biff Microsoft Excel MVP "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Ashish -
Thank you for the reply. I replicated the grid as you did (data in D6-F9, CF in D12, BLV in E12) and used your formula, but am getting a #VALUE error. Odd... -- Robert Robinson "Ashish Mathur" wrote: Hi, You can use this array formula (Ctrl+Shift+Enter). I have assumed that the data below is in range D6:F9. D12 and E12 have CF and BLV =INDEX($D$6:$F$9,MATCH(D12&E12,D6:D9&E6:E9,0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Excel does not seem to like the D6:D9 and E6:E9 arguments...
-- Robert Robinson "Ashish Mathur" wrote: Hi, You can use this array formula (Ctrl+Shift+Enter). I have assumed that the data below is in range D6:F9. D12 and E12 have CF and BLV =INDEX($D$6:$F$9,MATCH(D12&E12,D6:D9&E6:E9,0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Hi,
Did you do the Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... Excel does not seem to like the D6:D9 and E6:E9 arguments... -- Robert Robinson "Ashish Mathur" wrote: Hi, You can use this array formula (Ctrl+Shift+Enter). I have assumed that the data below is in range D6:F9. D12 and E12 have CF and BLV =INDEX($D$6:$F$9,MATCH(D12&E12,D6:D9&E6:E9,0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Yes.
Excel just did not seem to like the "Match" part of the formula -- Robert Robinson "Ashish Mathur" wrote: Hi, Did you do the Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... Excel does not seem to like the D6:D9 and E6:E9 arguments... -- Robert Robinson "Ashish Mathur" wrote: Hi, You can use this array formula (Ctrl+Shift+Enter). I have assumed that the data below is in range D6:F9. D12 and E12 have CF and BLV =INDEX($D$6:$F$9,MATCH(D12&E12,D6:D9&E6:E9,0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array with multiple selection criteria
Hi,
You may mail me the file at ask(at)ashishmathur(dot)com -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... Yes. Excel just did not seem to like the "Match" part of the formula -- Robert Robinson "Ashish Mathur" wrote: Hi, Did you do the Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... Excel does not seem to like the D6:D9 and E6:E9 arguments... -- Robert Robinson "Ashish Mathur" wrote: Hi, You can use this array formula (Ctrl+Shift+Enter). I have assumed that the data below is in range D6:F9. D12 and E12 have CF and BLV =INDEX($D$6:$F$9,MATCH(D12&E12,D6:D9&E6:E9,0),3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Robinson" wrote in message ... I have a 3 column list. Two columns have criteria which must be true in order to select the value of the third (numeric) column. For example: Sheet 1 Col 1 Col2 Amount CF AMI 1000.00 CI AMI 230.00 CF BLV 1500.00 CI BLV 160.00 I want to insert these values in a separate sheet, according to my selection rules, for example: Sheet 2 Cell A6 If column 1 = CF and column 2 = AMI insert contents of column 3 Cell A7 If column 1 = CF and column 2 = BLV insert contents of column 3 Cell B6 If column 1 = CI and column 2 = AMI insert contents of column 3 Cell B7 If column 1 = CI and column 2 = BLV insert contents of column 3 This is a very simple query, but my issue is that I am not sure if I should use a lookup or a nested IF statement, or if there is another function that I should use instead. Please advise. -- Robert Robinson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Criteria for lookup array | Excel Worksheet Functions | |||
| MODE fx in array using multiple criteria | Excel Worksheet Functions | |||
| Can I use an array formula with multiple criteria in the same row? | Excel Worksheet Functions | |||
| Array Formulas with multiple criteria in the same row? | Excel Worksheet Functions | |||
| SUMIF MULTIPLE ARRAY CRITERIA | Excel Worksheet Functions | |||
Linear Mode
