4 field lookup

[sdav]

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]

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))

 

[T. Valko]

Try this array formula** :

=INDEX('2009 Returns'!P$2$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$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.

 

[KC hotmail com>]

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.

 

[sdav]

i tried that and it still isn't working. Do you have another suggestion?

 

[KC hotmail com>]

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