4 field lookup
 

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)

4 field lookup
 

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

4 field lookup
 

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" wrote in message
...
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)

4 field lookup
 

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)

4 field lookup
 

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)

4 field lookup
 

Your final one (D2) has D2 listed twice by mistake. Just remove the
duplicate D2. Should read:
'2009 Returns'!$D$2:$D$5000
instead of
'2009 Returns'!$D$2:$D$2:$D$5000
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"sdav" wrote:

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)