Hard task for experts

elderill

New Member
Joined
Mar 9, 2011
Messages
7
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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.
 

elderill

New Member
Joined
Mar 9, 2011
Messages
7
Hi,

Thank you for the reply.
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?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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)
 

elderill

New Member
Joined
Mar 9, 2011
Messages
7
Yes, this is what I'm looking for.
Exactly!
But does it work when the two arrays are in different tabs?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Yes - just qualify the range with the respective sheet object, eg:

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

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top