Match two lists, skip #N/A, return list of matches

[mrhoden]

G'day everyone,

I am trying to compare two lists of students with the aim of returning a list of matches. The first list is in the array B8:B99 (with some blanks at the bottom) and the second list in found at D8:D99. The matches rarely occur on the same row. I'm trying to list the matches from B108:B199. In this case, Cell B107 tells us that there are 17 matches, but the formula pasted below tells us the matches occur after several #N/As. I'm hoping somebody might be able to tell me how to skip the #N/As in my new list. Here is what I have so far in B108:

=IF(ROWS(B$108:B108)>B$107,"",INDEX(B$8:B$100,SMALL(IF(MATCH(B$8:B$100,D$8:D$100,0),ROW(B$8:B$100)-ROW(B$8)+1),ROWS(B$108:B108))))

This returns #N/A because the match function returns this:

=IF(ROWS(B$108:B108)>B$107,"",INDEX(B$8:B$100,SMALL(IF({#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43;43}),ROW(B$8:B$100)-ROW(B$8)+1),ROWS(B$108:B108))))

Any help here would be greatly appreciated.

Marty

 

[AhoyNC]

Try wrapping your MATCH in an ISNUMBER function. Also, something looks off on your ROWS function. Since your data starts on row 8 seems like the ROWS(B$108:B108) should be ROWS(B$8:B8).

=IF(ROWS(B$108:B108)>B$107,"",INDEX(B$8:B$100,SMALL(IF(ISNUMBER(MATCH(B$8:B$100,D$8:D$100,0)),ROW(B$8:B$100)-ROW(B$8)+1),ROWS(B$108:B108))))

 

[mrhoden]

Thanks very much. I'll have a go.

 

[FlameRetired]

Another way. I assumed data in B2 and down and D2 and down, then array entered in F2.

=IFERROR(INDEX($D$2:$D$13,SMALL(IF(ISNA(MATCH($D$2:$D$13,$B$2:$B$13,0)),"",ROW($D$2:$D$13)-MIN(ROW($D$2:$D$13))+1),ROWS($2:2))),"")

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

List 1​

[/td][td][/td][td]

List 2​

[/td][td][/td][td]

Common​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

Tomato​

[/td][td][/td][td]

Beans​

[/td][td][/td][td]

Cabbage​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

Peas​

[/td][td][/td][td]

Corn​

[/td][td][/td][td]

Apple​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

Pear​

[/td][td][/td][td]

Cabbage​

[/td][td][/td][td]

Peas​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

Celery​

[/td][td][/td][td]

Apple​

[/td][td][/td][td]

Celery​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

Banana​

[/td][td][/td][td]

Peas​

[/td][td][/td][td]

Banana​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td]

Celery​

[/td][td][/td][td]

Lettuce​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

Lettuce​

[/td][td][/td][td]

Banana​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

Apple​

[/td][td][/td][td]

Carrots​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td]

Cabbage​

[/td][td][/td][td]

Watermelon​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td]

Kiwi​

[/td][td][/td][td]

Peach​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td]

Lettuce​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td]

Pineapple​

[/td][td][/td][td][/td][/tr]
[/table]

 

[Aladin Akyurek]

Try wrapping your MATCH in an ISNUMBER function. Also, something looks off on your ROWS function. Since your data starts on row 8 seems like the ROWS(B$108:B108) should be ROWS(B$8:B8).

=IF(ROWS(B$108:B108)>B$107,"",INDEX(B$8:B$100,SMALL(IF(ISNUMBER(MATCH(B$8:B$100,D$8:D$100,0)),ROW(B$8:B$100)-ROW(B$8)+1),ROWS(B$108:B108))))

Supposing that B107 houses a count (by a formula), the first instance of this formula augmented with ISNUMBER can be implemented in B108 with no problems at all. It would be a coherent way to do the anchoring.

 

[mrhoden]

Try wrapping your MATCH in an ISNUMBER function. Also, something looks off on your ROWS function. Since your data starts on row 8 seems like the ROWS(B$108:B108) should be ROWS(B$8:B8).

=IF(ROWS(B$108:B108)>B$107,"",INDEX(B$8:B$100,SMALL(IF(ISNUMBER(MATCH(B$8:B$100,D$8:D$100,0)),ROW(B$8:B$100)-ROW(B$8)+1),ROWS(B$108:B108))))

Worked perfectly. Thanks very much for your help!