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

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

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

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

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

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

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

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

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

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