Prev: Identify Whole Number
Next: Inserting pictures into Excel Sheet at a particular size
From: sdav on 24 Feb 2010 13:03
I have 2 spreadsheets (sales and returns) that I want to match 4 columns on
and if they match pull the P column from the Returns spreadsheet. I found
some old posts with a formula I tried to follow, but I get 0 everywhere. I
have no idea what this is doing. Can anyone help me? Thanks,


=IF(ISNUMBER(MATCH(1,('2009 Returns'!$A$2:$A$5000=A2)*('2009
Returns'!$B$2:$B$5000=B2)*('2009 Returns'!$C$2:$C$5000=C2)*('2009
Returns'!$D$2:$D$2:$D$5000 =D2),0)),'2009 Returns'!P2,0)
From: Dave Peterson on 24 Feb 2010 13:28
I like to use multiple columns--one for the long formula and one to retrieve the
value (and check to see if there was a match:

Try this in one cell (say X2)

=MATCH(1,(('2009 Returns'!$A$2:$A$5000=A2)
*('2009 Returns'!$B$2:$B$5000=B2)
*('2009 Returns'!$C$2:$C$5000=C2)
*('2009 Returns'!$D$2:$D$2:$D$5000=D2)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Then in the adjacent cell (Y2 in my example):
=if(iserror(x2),0,index('2009 returns'!$p$2:$p$5000,x2))
or
=if(iserror(x2),"no match",index('2009 returns'!$p$2:$p$5000,x2))









sdav wrote:
>
> I have 2 spreadsheets (sales and returns) that I want to match 4 columns on
> and if they match pull the P column from the Returns spreadsheet. I found
> some old posts with a formula I tried to follow, but I get 0 everywhere. I
> have no idea what this is doing. Can anyone help me? Thanks,
>
> =IF(ISNUMBER(MATCH(1,('2009 Returns'!$A$2:$A$5000=A2)*('2009
> Returns'!$B$2:$B$5000=B2)*('2009 Returns'!$C$2:$C$5000=C2)*('2009
> Returns'!$D$2:$D$2:$D$5000 =D2),0)),'2009 Returns'!P2,0)

--

Dave Peterson
From: T. Valko on 24 Feb 2010 13:34
Try this array formula** :

=INDEX('2009 Returns'!P$2:P$5000,MATCH(1,IF('2009
Returns'!A$2:A$5000=A2,IF('2009
Returns'!B$2:B$5000=B2,IF('2009 Returns'!C$2:C$5000=C2,IF('2009
Returns'!D$2:D$5000 =D2,1)))),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"sdav" <sdav(a)discussions.microsoft.com> wrote in message
news:EB4E75DC-7663-4221-A07B-0580BFED0CA2(a)microsoft.com...
>I have 2 spreadsheets (sales and returns) that I want to match 4 columns on
> and if they match pull the P column from the Returns spreadsheet. I found
> some old posts with a formula I tried to follow, but I get 0 everywhere. I
> have no idea what this is doing. Can anyone help me? Thanks,
>
>
> =IF(ISNUMBER(MATCH(1,('2009 Returns'!$A$2:$A$5000=A2)*('2009
> Returns'!$B$2:$B$5000=B2)*('2009 Returns'!$C$2:$C$5000=C2)*('2009
> Returns'!$D$2:$D$2:$D$5000 =D2),0)),'2009 Returns'!P2,0)


From: KC hotmail com> kcrippstein on 24 Feb 2010 13:50
When you enter the formula, try using Ctrl+Shift+Enter (we call this CSE)
instead of just Enter. This appears to be an array formula, which will
evaluate what you've specified line by line for rows 2:5000, but it won't
work unless you CSE.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"sdav" wrote:

> I have 2 spreadsheets (sales and returns) that I want to match 4 columns on
> and if they match pull the P column from the Returns spreadsheet. I found
> some old posts with a formula I tried to follow, but I get 0 everywhere. I
> have no idea what this is doing. Can anyone help me? Thanks,
>
>
> =IF(ISNUMBER(MATCH(1,('2009 Returns'!$A$2:$A$5000=A2)*('2009
> Returns'!$B$2:$B$5000=B2)*('2009 Returns'!$C$2:$C$5000=C2)*('2009
> Returns'!$D$2:$D$2:$D$5000 =D2),0)),'2009 Returns'!P2,0)
From: sdav on 24 Feb 2010 15:12
i tried that and it still isn't working. Do you have another suggestion?

"KC" wrote:

> When you enter the formula, try using Ctrl+Shift+Enter (we call this CSE)
> instead of just Enter. This appears to be an array formula, which will
> evaluate what you've specified line by line for rows 2:5000, but it won't
> work unless you CSE.
> --
> Please remember to indicate when the post is answered so others can benefit
> from it later.
>
>
> "sdav" wrote:
>
> > I have 2 spreadsheets (sales and returns) that I want to match 4 columns on
> > and if they match pull the P column from the Returns spreadsheet. I found
> > some old posts with a formula I tried to follow, but I get 0 everywhere. I
> > have no idea what this is doing. Can anyone help me? Thanks,
> >
> >
> > =IF(ISNUMBER(MATCH(1,('2009 Returns'!$A$2:$A$5000=A2)*('2009
> > Returns'!$B$2:$B$5000=B2)*('2009 Returns'!$C$2:$C$5000=C2)*('2009
> > Returns'!$D$2:$D$2:$D$5000 =D2),0)),'2009 Returns'!P2,0)
 |  Next  |  Last
Pages: 1 2
Prev: Identify Whole Number
Next: Inserting pictures into Excel Sheet at a particular size