View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Matching data from 2 columns

Just to make things a little harder, here is a way to do it
with letters in the data and array formulas.
This is even harder than Advanced Filter, but it is in real time.
Assuming your data is arranged like this:

List3 List4

45C 45E
45A 46C
46C 50B
48F 50F
50F 50F
51A 52C
54F 53C
54F 53F
55B 53C
54F
55B

Name List3, List4, including the blank row.
Name the following:
Set1 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List3)+1))
Set2 Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(List4)+1))
The Result1 and Result2 array formulas (CSE) are respectively:
=INDEX(List3,LARGE(IF((COUNTIF(List4,List3)0)*Set 1=0,1,Set1),Set1))
=INDEX(List4,LARGE(IF((COUNTIF(List3,List4)0)*Set 2=0,1,Set2),Set2))

Result1 Result2
55B 55B
54F 54F
54F 50F
50F 50F
46C 46C