4 field lookup

  • Thread starter Thread starter sdav
  • Start date Start date
S

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)
 
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))
 
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.
 
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.
 
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
 
Back
Top