IF Formula combination

Hi all,

Hope all is well. I would like to ask for some ideas as to the following problem.


Basically I have a spreadsheet with data written in two columns. Say as follows:



Column A Column B

1000 1010
1010 1020
1020 1030
1040 1040
1060 1050
1080 1070

Is there maybe a suggestion as to how I can create a formula or a set of formulas (maybe IF formulas or a combination) in Column C which which would include:
if the number is found in Column A but not in B, include in C
If the number is found in column B but not in A, include in C
If the number is found in both column A and B, include the number from any column

Therefore column C would look like:

Column A Column B Column C

1000 1010 1000
1010 1020 1010
1020 1030 1020
1040 1040 1030
1060 1050 1040
1080 1070 1050
----- ----- 1060
----- ----- 1070
----- ----- 1080

I don't know if this is possible using formulas. Thanks a lot for your ind help, appreciate a lot!

Keibri

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

I have a feeling I am missing something here, but lets try this as a start...

	A	B	C
2	1000	1010	1000
3	1010	1020	1020
4	1020	1030	1030
5	1040	1040	1040
6	1060	1050	1060
7	1080	1070	1080

C2=IF(ISERROR(MATCH(A2,$B$2:$B$7,0)),A2,IF(ISNUMBER(MATCH(B2,$A$2:$A$7,0)),B2,IF(COUNTIF($C1:C$2,A2)>=1,B2,A2)))
copied down

With your data in A2:B7 try array-entering this in C2 and filling down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Results are this.

Row\Col	A	B	C
2	1000	1010	1000
3	1010	1020	1010
4	1020	1030	1020
5	1040	1040	1030
6	1060	1050	1040
7	1080	1070	1050
8			1060
9			1070
10			1080

Edit A shorter version ... still array-entered ... would be

Formula:

Please Login or Register  to view this content.

You are welcome. Glad it does the job, and thank you for the feedback.