Hard task for experts

[elderill]

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.

 

[DonkeyOte]

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]

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]

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
	A	B	C	D
1	2300		abc 2300	2300
2	5959		def 5959	5959
3	1090		ghi 1090	1090
4	1333		jkl 1333	1333
5			mno 1234	#N/A
Sheet4

 

[DonkeyOte]

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

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

 

[elderill]

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

 

[DonkeyOte]

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

Sheet1!$A$1:$A$4

 

[elderill]

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