Search a column for content equal to a cell and return cell value adjacent

[ninsianna]

Not sure I described that well, but here's my example:

I have this cross referenced code and reference numbers:

Code	Ref numbers
OQ COR 01	7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.7
OQ COR 02	5.2, 7.6, 13, 13.1, 13.2, 13.3, 13.4, 13.5,
OQ COR 03	10, 10.1, 10.2, 11
OQ COR 04	8, 8.1, 8.3, 12,
OQ COR 05	5, 5.1, 5.2,5.3, 8, 8.1, 8.3,

<tbody>
</tbody>

I have a list of reference numbers:

Ref #

4.3 5.1 5.2 5.3 7.1 7.2 7.3 7.4 7.5 <tbody> </tbody>

<tbody>
</tbody>

I would like to search the Ref numbers to see if the Ref # is included, and if so, return the contents of the adjacent cell.

So : if the contents of C1 are included in column Y, in cell Y3, then return the contents of X3

The biggest issue is of course that the contents in column Y include multiple reference #s, so I'm not looking for an exact match. Even if I split them into individual columns, I'm not getting an accurate result with lookup or vlookup

 

[markmzz]

Try this:

Layout

Code	Ref numbers		Ref #	Codes
OQ COR 01	7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.7		4.3
OQ COR 02	5.2, 7.6, 13, 13.1, 13.2, 13.3, 13.4, 13.5,		5.1	OQ COR 05
OQ COR 03	10, 10.1, 10.2, 11		5.2	OQ COR 02	OQ COR 05
OQ COR 04	8, 8.1, 8.3, 12,		5.3	OQ COR 05
OQ COR 05	5, 5.1, 5.2,5.3, 8, 8.1, 8.3,		7.1	OQ COR 01
			7.2	OQ COR 01
			7.3	OQ COR 01
			7.4	OQ COR 01
			7.5	OQ COR 01
			8.3	OQ COR 04	OQ COR 05
**********	**************************************	**	*****	***********	***********	***********

<colgroup><col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="201" style="width: 151pt; mso-width-source: userset; mso-width-alt: 7350;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" span="3"> <tbody> </tbody>

Formula

In E2 - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX($A$2:$A$6,
SMALL(IF(ISNUMBER(SEARCH(","&$D2&",",","&SUBSTITUTE($B$2:$B$6," ","")&",")),ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($E2:E2))),"")

And copy to the right and down.

Markmzz

 

[ninsianna]

Thank you for your quick response, and my apologies for my ignorance. I'm just not that experienced with the advanced formula features of excel. Using the formula you provided with the same layout I get the following results, where the Code returned is the same code on that row, as opposed to the code in the cell adjacent to the Ref numbers containing the Ref#. I've been playing around with it a bit, and still am not getting the correctly referenced cell.

Code	Ref numbers		Ref #	Codes
OQ COR 01	7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.7		4.3	OQ COR 01
OQ COR 02	5.2, 7.6, 13, 13.1, 13.2, 13.3, 13.4, 13.5,		5.1	OQ COR 02
OQ COR 03	10, 10.1, 10.2, 11		5.2	OQ COR 03
OQ COR 04	8, 8.1, 8.3, 12,		5.3	OQ COR 04
OQ COR 05	5, 5.1, 5.2,5.3, 8, 8.1, 8.3,		7.1	OQ COR 05
			7.2
			7.3
			7.4

<tbody>
</tbody>

 

[Aladin Akyurek]

Codes and ref numbers are assumed to be in X:Y with headers in the first row.

Refs for which codes must be returned in colum C from row 1 on.

D1, copied down:

=IFERROR(LOOKUP(9.99999999999999E+307,
  SEARCH(","&C1&",",","&SUBSTITUTE(Sheet1!$Y$2:$Y$6," ","")&","),
  Sheet1!$X$2:$X$6),"Not Found")

See:

https://dl.dropboxusercontent.com/u/65698317/ninsianna lookup search.xlsx

 

[markmzz]

Thank you for your quick response, and my apologies for my ignorance. I'm just not that experienced with the advanced formula features of excel. Using the formula you provided with the same layout I get the following results, where the Code returned is the same code on that row, as opposed to the code in the cell adjacent to the Ref numbers containing the Ref#. I've been playing around with it a bit, and still am not getting the correctly referenced cell.

Code	Ref numbers		Ref #	Codes
OQ COR 01	7, 7.1, 7.2, 7.3, 7.4, 7.5, 7.7		4.3	OQ COR 01
OQ COR 02	5.2, 7.6, 13, 13.1, 13.2, 13.3, 13.4, 13.5,		5.1	OQ COR 02
OQ COR 03	10, 10.1, 10.2, 11		5.2	OQ COR 03
OQ COR 04	8, 8.1, 8.3, 12,		5.3	OQ COR 04
OQ COR 05	5, 5.1, 5.2,5.3, 8, 8.1, 8.3,		7.1	OQ COR 05
			7.2
			7.3
			7.4

<tbody>
</tbody>

First, did you press Ctr+Shift+Enter to enter the formula?

Second, could you post the formula that you used?

Markmzz

 

[lizardbreath]

First, did you press Ctr+Shift+Enter to enter the formula?

Second, could you post the formula that you used?

Markmzz

Hello,

I am trying something very similar to this but with email addresses.

Here is what I have.

Column A has an ID #

Column B has a set of email addresses

Column C has a set of email addresses

If column B has a match anywhere in Column C then I want Column D to display the ID # that is in Column A of the match.

 

[markmzz]

Hello,

I am trying something very similar to this but with email addresses.

Here is what I have.

Column A has an ID #

Column B has a set of email addresses

Column C has a set of email addresses

If column B has a match anywhere in Column C then I want Column D to display the ID # that is in Column A of the match.

Could you post a small example?

Use the table below for that.

ID#	Email	**	Email	ID's
		**
		**
		**
		**
		**
		**
		**
		**
		**
		**
*******	**************************************	**	**************************************	*******	*******	*******

<tbody>
</tbody>

Markmzz

 

[lizardbreath]

Could you post a small example?

Use the table below for that

ID#	Email	**	Email	ID's
12134	test@yahoo.com	**	tester@yahoo.com	88888
99999	Happy@yahoo.com	**	test@yahoo.com	12134
88888	tester@yahoo.com	**	Happy@yahoo.com	99999
		**
		**
		**
		**
		**
		**
		**
*******	**************************************	**	**************************************	*******	*******	*******

<tbody>
</tbody>

Markmzz

Here you go - Let me know if you have further questions. Basically if there is an email address in Column C that has a match in Column A. Then I want it to return the corresponding ID to the left of Column A in Column D.

 

[markmzz]

Here you go - Let me know if you have further questions. Basically if there is an email address in Column C that has a match in Column A. Then I want it to return the corresponding ID to the left of Column A in Column D.

Try this:

Layout

ID#	Email	**	Email	ID's
12134	test@yahoo.com	**	tester@yahoo.com	88888
99999	Happy@yahoo.com	**	test@yahoo.com	12134
88888	tester@yahoo.com	**	Happy@yahoo.com	99999
*******	********************	**	********************	*******

<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody> </tbody>

Formula

In E2

=IFERROR(INDEX($A$2:$A$4,MATCH($D2,$B$2:$B$4,0)),"")

Markmzz

 

[lizardbreath]

Try this:

Layout

ID#	Email	**	Email	ID's
12134	test@yahoo.com	**	tester@yahoo.com	88888
99999	Happy@yahoo.com	**	test@yahoo.com	12134
88888	tester@yahoo.com	**	Happy@yahoo.com	99999
*******	********************	**	********************	*******

<tbody>
</tbody>

Formula

In E2

=IFERROR(INDEX($A$2:$A$4,MATCH($D2,$B$2:$B$4,0)),"")

Markmzz

That did not work for me. It returned blank data.