Find a value in a range of cells and if it doesn't find it return a specifeied row

[lwhyatt]

I have a workbook with 3 worksheets A, B and C. I want to look up values from column 1 of worksheet B and if it doesn't find a match in column 1 of worksheet A, then return the information contained in the row of its entry in worksheet B. The results are to appear in new worksheet C without blank rows. Can anybody suggest an appropriate formula to use?

Many thanks in advance

Lyndon

 

[wideboydixon]

Book1
	A	B
1	2	A
2	3	A
3	5	A
4	7	A
A

Book1
	A	B
1	1	B
2	2	B
3	3	B
4	4	B
5	5	B
6	6	B
7	7	B
8	8	B
9	9	B
10	10	B
B

Book1
	A	B
1	1	B
2	2	A
3	3	A
4	4	B
5	5	A
6	6	B
7	7	A
8	8	B
9	9	B
10	10	B
C

Cell Formulas
Range	Formula
A1	=B!$A1
B1	=IFERROR(INDEX(A!$B:$B,MATCH($A1,A!$A:$A,0)),B!$B1)

WBD

 

[lwhyatt]

Not quite..


Worksheet C would look like

1 B
6 B
7 B
8 B
9 B
10 B

since 1,6,7,8,9,10 only do not appear in worksheet A compared to worksheet B, and they are all B as this data has been bought over from their respective rows in worksheet B to the new worksheet C.

 

[wideboydixon]

OK. Try this in A1 on sheet C:

=IFERROR(INDEX(B!A$1:A$10,SMALL(IF(NOT(ISNUMBER(MATCH(B!$A$1:$A$10,A!$A$1:$A$4,0))),ROW(B!$A$1:$A$10)),ROWS($A$1:$A1))),"")

Enter with Ctrl+Shift+Enter to create an array formula. Change the $10 to be the last row on Sheet B and the $4 to be the last row on Sheet A. Copy across and down on Sheet C as necessary.

WBD

 

[lwhyatt]

thankyou