get data when 2 conditins are met?

[Guest]

I want to perform the following task:
chk to insure that a number in column a on sheet 1 exists on column 1on sheet2
(on sheet 2 the number in question will appear on more than one row),
if no leave blank,
if yes check in the sucessful rows if another particular number exists in
column c
if no leave blank
if yes give the number that is in column f that meets the above 2 criterions
(there will only be one row that meets both of the above criterions)

I was playing around with something like this, but no go
=IF((AND(VLOOKUP(A5,TEST!A:A,1,FALSE=A5),(VLOOKUP(A5,TEST!A:C,3,FALSE=25)))),TEST!,P2)

thanks in advance,
R.

 

[Frank Kabel]

Hi
try the following array formula(entered with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,('sheet2'!A1:A100=A1)*('sheet2'!C1:C100=B1),0)),"",IND
EX('sheet2'!F1:F100,MATCH(1,('sheet2'!A1:A100=A1)*('sheet2'!C1:C100=B1)
,0)))

 

[Guest]

thanks, I will attempt to change the variables to match my sheet name and
locations...when I go to enter it I paste it in and press ctrl shift enter
and it does not seem to activate....what am I doing wrong? Also, ouit of
curiosity, what does the * do in the formula? Is this what allows the second
condition?
R.

 

[Ken Wright]

How about:-

=IF(SUMPRODUCT((Sheet2!$A$1:$A$29=Sheet1!A1)*(Sheet1!$C$1:$C$29=25)*(Sheet1!$F$1:$F$29))=0,"",SUMPRODUCT((Sheet2!$A$1:$A$29=Sheet1!A1)*(Sheet1!$C$1:$C$29=25)*(Sheet1!$F$1:$F$29)))

 

[Frank Kabel]

Hi
what result are you getting?
The '*' is to create an AND condition within an array formula

 

[Aladin Akyurek]

Try the multi-key approach using ordinary, faster retrieval formulas:

http://www.mrexcel.com/board2/viewtopic.php?t=90715&highlight=multikey

 

[Harlan Grove]

Try the multi-key approach using ordinary, faster retrieval formulas:

http://www.mrexcel.com/board2/viewtopic.php?t=90715&highlight=multikey

....

You should mention that your ordinary, faster retrieval formulas come at the
cost of greater persistent memory usage. Your approach involves using extra
cells to hold keys concatenated from multiple columns in a base table.
Frank's approach may be somewhat slower, but it uses less memory except
during recalc.

 

[Aladin Akyurek]

Yes, the setup trades off cell space against speed. The qualification as
such is available in some of the posts by me as well as by others.

 

[Guest]

Thanks Frank, I got it working with your formula.

Question1: Is it possible to use something for the lookup array that covers
the entire column (other than A1:A100)? On some formulas I've used A:A but it
doesn't seem to work here.

Question2: Why is the lookup value 1 in this formula? Is that 1 indicating
the first row from the left? Just curious....

thanks,
R.

 

[Frank Kabel]

Hi to your questions:
1. No you can't as arrays can maximal span 65535 cells (and a column
has 65536). Bot for most pratical issues you could use a reference such
as A1:A65000

2. No, the '1' indicates the lookup_value. To explain this a little bit
more see the following layout:
A B C
1 A Z 10
2 B E 20
3 A Y 30
4 D Y 40
5 G Z 50

no lets assume we are searching for the value 'A' in column A and 'Y'
in column C (so in our example row 3 and column C would be '30').
The array formula would be:
=INDEX(C1:C5,MATCH(1,(A1:A5="A")*(B1:B5="Y"),0))

now lest see how the part (A1:A5="A")*(B1:B5="Y") evaluates. Thes are
two conditions which individually return TRUE or FALSE. So in our
example:
TRUE*FALSE
FALSE*FALSE
TRUE*TRUE
FALSE*TRUE
FALSE*FALSE

If you use a mathematical operation on boolean values (like a
multiplication) Excel coerces the boolean values to real numbers
(TRUE=1/FALSE=0). So our matrix from above becomes:
1*0=0
0*0=0
1*1=1
0*1=0
0*0=0

And as we're searching for '1' it returns the 3rd row. Only there all
conditions are met '1' is returned from the mutliplied conditions.

 

[Guest]

Frank,
Excellent explanation! I get it! The formula in my sheet works great. Do
you know of a good web reference of examples of other ways to use
"index/match"?

thanks again,
R.