Ask Your Question

Returning contents of row below if specific text is found in a columns' list

asked 2017-05-29 14:21:35 +0200

dealerz gravatar image


I am new here, and I have searched the forums to check whether this question has been dealt with but I can't seem to find the exact answer.

I have a long list of customer IDs (ALLINFO) and other details (of which I want the email addresses). Unfortunately, the associated email addresses are mixed in with this long list of IDs.

All data is in column A of a calc sheet I have. I have managed to pull all the IDs to a column of a new sheet (customerinfo, col A), and I would like to now paste the email address associated to each of these IDs in to a new column (col B).

The list of info is such that when there is an email address, I know for sure that it belongs to the customer ID just above it. I assume I have to do a MATCH INDEX mix, but I don't understand how I can ask it to pull out the cell content containing the email address which is just below it.

So in my new sheet I would like to have something like this:

Customer ID_A - If [Customer ID_A is in list on sheet ALLINFO, give me the row just below it, else nothing] Customer ID_B - If [Customer ID_B is in list on sheet ALLINFO, give me the row just below it, else nothing] and so on.

Do I have to do a SEARCH, MATCH, INDEX, LOOKUP ?? Am very confused as to what's the best way forward.

Thanks for any help you can give.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-05-29 15:22:04 +0200

erAck gravatar image

updated 2017-05-30 15:20:12 +0200

Assuming your data is in A1:A9 and the search criterion (customer ID) is in C1, then this =OFFSET(A1;MATCH(C1;A1:A9;0);0) will return the cell just below the match.

To create a column of data use =IFERROR(IF(SEARCH("ID",A1,1)=1,OFFSET(A1,1,0),""),"") and pull/copy that formula down.

Btw, would be nice if additional information was not given as answer but by editing the original question instead.

edit flag offensive delete link more

answered 2017-05-30 12:28:16 +0200

dealerz gravatar image

updated 2017-05-30 14:16:49 +0200

Thanks for replying! I tried this and it works (and I learnt a new trick!) but it's a little different to what I wanted to do :-)

Since the list is really long (4000 rows) and I don't want to have to input an ID one at a time to 'grab' the email, I decided to filter out all the email addresses by looking for the '@', copying the list to an editor, eliminating the spaces using the 'lines operation' tool, copying the list back to a new calc sheet in column B next to a new col A which holds the custIDs. I filtered the IDs out in the exact same way. Really clumsy I know, and my libreoffice program crashed doing it many times (it can't seem to handle massive datasets?).

=IF(ISNUMBER(SEARCH(".@.",A1)),A1,"") -// A1 is the start of the first customer ID and will return blank since it doesn't contain an email address. Do the same to extract the IDs to create a list of just IDs //

A1-A4000 contains a long list like this:

  • List item

[some other info which can vary]
[some other info maybe]

and I wanted to move the IDs to a new column, and then move the email addresses (just below) next to the IDs, without changing the order obviously. The result being a nice list with no empty rows of IDs and a nice list of email addresses.

I would have preferred to have done it much more efficiently but I was stumped on how to transfer from column A all the IDS without spaces into a fresh column, D, let's say, and then transfer the associated email address in to col E, using a formula for all the cells down.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-29 14:21:35 +0200

Seen: 605 times

Last updated: May 30 '17