Search Column for Specific Text

[kalebsmith10]

Hi,

I am looking for a formula that will be on sheet "1N - Rack 3 - Generic". I want it to search in sheet "1 South". I want it to search the entire M Column for a "Specific Text". There should only be 1 match. When it finds this match, I want to copy the text from Column A on Sheet "1 South" to the cell with the formula on Sheet "1N - Rack 3 - Generic".

I know this sounds really confusing so I attached screen shots of what I'm trying to do. Basically I'm looking for a formula that will auto fill the Rack page with the corresponding device. So, if the screenshots you can see the formula I am currently using to fill B4. It's looking for "R3-P6/01" and then returning the value of Column A. This does work, however, I have to specify the exact cell instead of it searching the entire row for that value.

 

[MisterProzilla]

Try this:

=INDEX('1South'!$A$1:$M$10,MATCH("C",'1South'!$M$1:$M$10,0),1)

Where A1:M10 is your entire range in 1South, M1:M10 should be the same size vertically. Replace "C" with whatever text you're searching for, or alternatively replace it with an absolute reference to a cell containing the text you're after. Let me know if that works

 

[kalebsmith10]

I just figured it out and basically used what you stated.

=INDEX('1 South'!A3:A500,MATCH("*R3-P6/02*",'1 South'!M3:M500,))

Thanks!

 

[MisterProzilla]

Nice one