# Can you serach an array and extract a value and input that value into another cell?

1. ## Can you serach an array and extract a value and input that value into another cell?

Hello

I'm trying to cut and paste data into a workbook without having to manually type in the data into each cell. When I copy the data it pastes all along column A.

From column A, I'd like to extract certain data to column H.

Problem: The data is not always in a specific cell, such as listed here (attached) in cell A29. It could be in A27:A32. Is there function that will allow me to search an array for a specific value (in this case a date), extract a value, and input that value into a certain cell, such as the date in H29?  Register To Reply

2. ## Re: Can you serach an array and extract a value and input that value into another cell?

hi there. what would be in other cells? you can use the MATCH formula to find a certain keyword. i used ":" in this case:
=MID(INDEX(A27:A32,MATCH("*:*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*:*",A27:A32,0)))+1,100)

or even find the date format:
=MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+1,100)

but do note that LEFT, RIGHT, and MID will make the extraction a text. Dates are better handled as numbers. to convert them, simply put double negations in front:
=--MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+2,100)
format it as a date.
i also just realised it should be "+2" instead of "+1" since the date is 2 characters from ":"  Register To Reply

3. ## Re: Can you serach an array and extract a value and input that value into another cell?

Thank you!

This worked for the date: =--MID(INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*/*/*",A27:A32,0)))+2,100)

What if I wanted to find only one of several possible text strings in A27:32, such as "Blue," "Red," or "Orange." Only one of those 3 possibilities would exist in that range and I'd like to capture it and insert it into a particular cell. Thinking I'd use the match formula below and instead of "*:*", what would it be?

=MID(INDEX(A27:A32,MATCH("*:*",A27:A32,0)),FIND(":",INDEX(A27:A32,MATCH("*:*",A27:A32,0)))+1,100)  Register To Reply

4. ## Re: Can you serach an array and extract a value and input that value into another cell?

Can you also take a look at this for me please? Cell A10 varies. It appears in Cell A8:A12. I'm currently using a search vlookup formula to correlate it to its corresponding location, but that only works if the data remains in one cell. I can't figure out how to combine your match formula w/ the vlookup. See attached.  Register To Reply

5. ## Re: Can you serach an array and extract a value and input that value into another cell?

I am just guessing that "Cell A10 varies. It appears in Cell A8:A12." means the text in A10 can appear multiple times and that you need to find the values from column K that match each occurrence. If that is the case then the following proposed solution works.
A helper column, which may be moved and/or hidden for aesthetic purposes, is added and populated using*: =SEARCH(J2,A\$10:A\$12)
The following array entered formula* populates H10:H12
Formula:  `Please Login or Register  to view this content.`

*Array entered formulas (and functions) are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.  Register To Reply

6. ## Re: Can you serach an array and extract a value and input that value into another cell?

Thank you JeteMc.  Register To Reply

7. ## Re: Can you serach an array and extract a value and input that value into another cell?

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.  Register To Reply