MATCH, LOOKUP, macro?

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

Ed from AZ

I have a list of 176 items and another list of 870 items. I need to
see if the items in the smaller list are on the bigger list. There is
no guarentee of any ascending or descending sort order. And I need to
match two values in the same row.

So:
IF (A2 is in B2:B871) _
AND **in the same row** C2 matches the value in col D _
THEN return "Yes"

MATCH and LOOKUP seem to require a sort order. And I'm not sure how
to use the row number with those. I could cobble together a macro,
but I thought a built-in function might be faster and easier (not to
mention I might actually learn something!).

Any help is greatly appreciated.
Ed
 
I would do the macro but
Just use this where n1:n3 is your big list and L1 is the first in the short
list and the formula is in m1,copied down.

=COUNTIF(N1:N3,L1)
to return the number in the long list
or
=if(COUNTIF(N1:N3,L1)>0,"yes","")
 
Assuming that you want A2 & C2 to index up as you drag the formula down try:

=IF(SUMPRODUCT(($B$2:$B$871=A2)*($D$2:$D$871=C2)),"Yes","")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top