#### elderill

##### New Member
Hi everyone,

I'm stuck with a task which I have to do in Excel. Please, help if you can.

I have two arrays in two different tables. What I need:

1. I want to check the value for each cell one of the tables if it matches any value from the other table.

2. If yes - then I want to know which is the value from the whole array that is matching.

I tried several variants with FIND, OR, MATCH in different combinations, but couldn't figure it out.

Could you help with this? I would be really grateful.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
elderill said:
1. I want to check the value for each cell one of the tables if it matches any value from the other table.

2. If yes - then I want to know which is the value from the whole array that is matching.

If the values match wouldn't the matching value simply be the original value ?

You can use COUNTIF or ISNUMBER & MATCH to test for matching items.

Perhaps best to elaborate on the original post with an example.

Hi,

Here is an example of what I mean:

Table 1:

2300
5959
1090
1333

Table 2: cells have strings, containing the values from table 1

"abc 2300"
"def 5959"
"ghi 1090"
"jkl 1333"

I want to check for each cell of table 2, if it contains some of the values from table 1. And if yes - then to know which is the value that matches:

For eaxmple:
If string "abc 2300" contains 5959, 1333, 1090 or 2300. If TRUE - then which is the value that is matching?

Still not entirely sure I follow which string is being compared to which but you may find a LOOKUP construct will work for you, eg:

Excel Workbook
ABCD
12300abc 23002300
25959def 59595959
31090ghi 10901090
41333jkl 13331333
5mno 1234#N/A
Sheet4

As an afterthought - if you have consistent delimiters (eg space) you should really look to append the strings so as to remove risk of false positives

Code:
``=LOOKUP(9.99E+307,SEARCH(" "&\$A\$1:\$A\$4&" "," "&\$C1&" "),\$A\$1:\$A\$4)``

Yes, this is what I'm looking for.
Exactly!
But does it work when the two arrays are in different tabs?

Yes - just qualify the range with the respective sheet object, eg:

Code:
``Sheet1!\$A\$1:\$A\$4``

Many thanks! This is really magical! ;o)))

Replies
3
Views
152
Replies
1
Views
160
Replies
0
Views
321
Replies
1
Views
267
Replies
10
Views
484

1,220,987
Messages
6,157,239
Members
451,407
Latest member
vdaesety

### 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