# Check if value is available in range, if yes, copy the corresponding data to a list

1. ## Check if value is available in range, if yes, copy the corresponding data to a list

Hello

I am desperately looking for a formula that checks:
"if a text string is available in a specific range of cells, copy the corresponding data to a list"

I am stuck on the fact that I don't have one cell to check, there are a couple of cells in one row on which the requirement is applied.

Example and intended end-result on my Drive:

Due to initial file upload problems, xlsx file is uploaded in a post below.

Ebru

2. ## Re: Check if value is available in range, if yes, copy the corresponding data to a list

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

3. ## Re: Check if value is available in range, if yes, copy the corresponding data to a list

with attachment

4. ## Re: "If value is present in a range, if yes, copy the corresponding data to a list"

Dear
My goal is to apply a formula on the above attached example which filters a list on mentioning of a specific number
I am really stuck at it.
Do I need INDEX MATCH or others?

Any clue is appreciated

Ebru

5. ## Re: Check if value is available in range, if yes, copy the corresponding data to a list

Not sure I fully understand how you want the results shown, but see if this will get you started>
F2=IF(LEFT(A2,2)="AB",A2,"")
copied down and across as needed

6. ## Re: Check if value is available in range, if yes, copy the corresponding data to a list

The formula is more complex:
it should go as:
check for each row
in columns A to E
if there is one value on that row which starts with "AB" (businesswise there can only be 0 or 1 cell "AB" label in that range)
copy that value and the corresponding data in columns F:I of that row.

This is a very complex formula, but it helps in giving all relevant data linked to "AB", which is relevant for me.

Ebru

7. ## Re: Check if value is available in range, if yes, copy the corresponding data to a list

This proposal employs helper columns (J2:N13) which may be moved and/or hidden for aesthetic purposes.
J2:J13 are populated using*: =IFERROR(INDEX(A2:E2,MATCH("AB",LEFT(A2:E2,2),0)),"")
*denotes an array entered formula which needs to be activate by simultaneously pressing the Ctrl, Shift and Enter keys before being copied down the column.
K2:N13 are populated using: =IF(J2="","",F2)
J19:J25 are populated using: =IFERROR(INDEX(J\$2:J\$13,AGGREGATE(15,6,(ROW(\$2:\$13)-1)/(J\$2:J\$13<>""),ROW(1:1))),"")
K19:N25 are populated using: =INDEX(K\$2:K\$13,MATCH(\$J19,\$J\$2:\$J\$13,0))
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)