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

	A​	B​	C​	D​	E​	F​
1​		List 1​		List 2​		Common​
2​		Tomato​		Beans​		Cabbage​
3​		Peas​		Corn​		Apple​
4​		Pear​		Cabbage​		Peas​
5​		Celery​		Apple​		Celery​
6​		Banana​		Peas​		Banana​
7​				Celery​		Lettuce​
8​		Lettuce​		Banana​
9​		Apple​		Carrots​
10​		Cabbage​		Watermelon​
11​		Kiwi​		Peach​
12​				Lettuce​
13​				Pineapple​

 

[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!