Finding Specific Text Within A Column And Reporting Where That Cell Is

[South Nashua]

Hi all, first time posting. Wondering if you could help.

Okay, I have a column of with cells filled with text. Let's call that Column A.

In another column, I have more cells filled with text. Let's call that Column B.

I want to put a formula in Column C find where an adjacent specific cell in Column B shows up in Column A (if at all) and what specific cell in Column A that is.

Example:

Dog	Dog
Cat	Pig
Pig	Blue

<tbody>
</tbody>

C1 would lookup B1 and equal A1. C1 would lookup B1 and equal A3. C3 would lookup B3 and equal FALSE.

I've tried combinations of VLOOKUP and FIND and SEARCH and MATCH and INDEX, but it's beyond my abilities. Hoping you all could help.

 

[Rick Rothstein]

Give this formula a try (adjust the A$1:A$3 range as needed but keep the $ signs)...

=IFERROR("A"&MATCH(B1,A$1:A$3,0),"")

 

[South Nashua]

It worked! Thanks!

I've got thousands of cells in each column, this will be a lifesaver.

 

[Peter_SSs]

Welcome to the MrExcel board!

A slight word of caution. If for some reason, any row(s) are subsequently added at the top of the sheet (eg to add headers) those formulas will be pointing to the wrong matching cells.

Here I have the suggested formula in column C and an alternative in column D. Both return the correct locations.

Excel 2010 32 bit
	A	B	C	D
1	Dog	Dog	A1	A1
2	Cat	Pig	A3	A3
3	Pig	Blue
4
Address of match

Cell Formulas
Range	Formula
C1	=IFERROR("A"&MATCH(B1,A$1:A$3,0),"")
D1	=IFERROR(ADDRESS(MATCH(B1,A:A,0),1,4),"")

.. and here is the same sheet with a new row 1 inserted.

Excel 2010 32 bit
	A	B	C	D
1	List 1	List 2	Match	Match
2	Dog	Dog	A1	A2
3	Cat	Pig	A3	A4
4	Pig	Blue
5
Address of match

Cell Formulas
Range	Formula
C2	=IFERROR("A"&MATCH(B2,A$2:A$4,0),"")
D2	=IFERROR(ADDRESS(MATCH(B2,A:A,0),1,4),"")