# Finding values in multiple columns

#### SBuckley

##### New Member
So I am trying to find numbers that appear in more than two columns, I have a formula that works for two columns and I need to expand on that so that it looks into three columns.
The formula I have gives me either the number if it is found in the other column or a blank if it only appears in the one.

Here is the formula:

Any help is greatly appreciated.

Thanks,

Scott

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I am not sure I have understood your problem. see data here and formula in E1 (which is copied down). Is this what you want. This is only if a value in columns A is available in column B OR column C

Sheet1

 * A B C D E 1 6 5 7 * * 2 10 9 1 * 10 3 5 8 5 * 5 4 4 4 2 * 4 5 3 1 10 * * 6 2 10 1 * 2 7 1 10 2 * 1 8 6 2 9 * * 9 5 10 8 * 5 10 7 4 4 * 7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula E1 =IF(IF(ISERROR(MATCH(A1,\$B\$1:\$B\$10,0)),0,MATCH(A1,\$B\$1:\$B\$10,0))+IF(ISERROR(MATCH(A1,\$C\$1:\$C\$10,0)),0,MATCH(A1,\$C\$1:\$C\$10,0))=0,"",A1)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I will try to play around with that formula to get what I want, I just want to get the numbers that appear all three times and not the ones that appear twice. This is because I will need to do one thing with the numbers I get from the equation I posted but then I have to do something else with the second set. Below is the table with what I'm looking to get.

 A B C D 55 100 100 100 66 55 89 77 44 66 66 100 66 33 89 29 55 55

<tbody>
</tbody>

And column D is where the formula would be applied to look through column C.

Ok just for a head's up and for anyone else who may ever have the same question. I was able to fix the code to search all three columns and find the matching values in all three.

=IF(AND(MATCH(C1,\$B\$1:\$B\$5,0),MATCH(C1,\$A\$1:\$A\$5,0)),C1,"")

Replies
1
Views
166
Replies
6
Views
519
Replies
1
Views
331
Replies
1
Views
597
Replies
25
Views
801

1,196,131
Messages
6,013,631
Members
441,777
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back