0

I have a huge data set. I need to be able to search a row of data for certain words and if that word is contained in the row I need it to copy that whole cell. If that word is not contained in the row I need it to either stay blank or give me a 0. Either way is fine. Each word though also contains a number next to it, thats why I need it to copy the whole cell so that the number is included. It looks like this:

Row 1 -- 5 Notropis 6 Cyprinella 10 Catfish 7 Machrybopsis

.... and so on.

I need to search row 1 for example for the word "Cyprinella".. if this word exists (which is does in this example, I want it to output "6 Cyprinella".

If I searched this same row for the word "Plecostomous" (which is not contained in the example row, I want it to stay blank or say "0".

Improve this question
2
  • 1
    THIS will get you started Commented Nov 26, 2013 at 22:17
  • Not much. I am so new to excel (at least more complex functions of excel) I don't even know where to begin. Commented Nov 26, 2013 at 22:53

1 Answer 1

Reset to default
1

If your data is in Row1, your search term in say F4 then: 

=IFERROR(INDEX(1:1,MATCH("*"&F4,1:1,0)),"")

should work.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Tried this... didn't seem to work, however I am pretty new to excel and have not done very much with it.
Yes. My row 1 does not contain data that I want to search for. So say I want to search for the word "catfish" in row 20.. would I type it in: =IFERROR(INDEX(1:20,MATCH(""&catfish,1:20,0)),"") I guess I am unclear what the 1:20, "" , and the final "" are all for... sorry I am such a noob.
HOLY MOLY... this worked... thank you so much! OK another dumb question... If I want to now search every other row below this for the same word (catfish) how can I do this? I tried the obvious drag down or double click the little black square but it keeps changing the formula. For example my original formula is: =IFERROR(INDEX(61:61,MATCH(""&V1,61:61,0)),"") When I extrapolate this down it changes it to =IFERROR(INDEX(62:62,MATCH(""&V2,62:62,0)),"") =IFERROR(INDEX(63:63,MATCH("*"&V3,63:63,0)),"") ..... and so on down the list... Is there any way to combat this? Thanks!!
Starts on Row 2 ends on Row 478 Columns K-U
This worked perfect!! Thank you so much for your time. This is literally going to save me weeks on this project.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.