Look for a value in a multiple line cells

[AliaNi]

Hi

I have a dataset of 200 rows.
Each cell has multiple lines, I want to write a formula that finds the line which has a specific word, and then get the corresponding value from the cell next to it. Is that possible?

Row 1:
Cell 1 Cell 2
A v
B d
C e

Row 2:
Cell 1 Cell 2
B d
C e

I want to find the vals (d) from cell 2 based on cell 1 (B)

Is this possible?

 

[Peter_SSs]

Welcome to the MrExcel board!

Your description, sample data and image do not seem to match each other very well.

Any chance that you could clarify by providing new sample data and the expected result with XL2BB and provide a new, clear, explanation of why that is the expected result?

 

[Micron]

If the lookup value is B and is in the 2nd line of row1, find what's in the 2nd line in the next cell of the same row1. At least that's my interpretation.
If a formula can handle this I will once again be amazed with what people around here can do in a formula.

 

[AliaNi]

Welcome to the MrExcel board!

Your description, sample data and image do not seem to match each other very well.

Any chance that you could clarify by providing new sample data and the expected result with XL2BB and provide a new, clear, explanation of why that is the expected result?

Thank you for answering. This is a part of the dataset I have.

I want to take out the times corresponding to scheduled, started and ended. I have 200 rows so I can't do it manually. Text to column does not work as not all the data is organized the same.

How would you do it?

 

[Peter_SSs]

Looks like you have shown new sample data, but not ..

and the expected result with XL2BB and provide a new, clear, explanation of why that is the expected result?

XL2BB would also be better because then we could esily copy the data for testing.

 

[AliaNi]

Looks like you have shown new sample data, but not ..



XL2BB would also be better because then we could esily copy the data for testing.

Book1.xlsx
	A	B	C	D	E
1			Started	Ended	Scheduled
2	Scheduled Started Ended	10:00 am 10:15 am 11:00 am
3	Started Ended	3:00 pm 4:00 pm
4	Started Ended Scheduled	1 pm 2 pm 3 pm
Sheet1

 

[Peter_SSs]

Thanks for getting XL2BB going but you still have not included the expected result(s) and told us how you got those results manually

 

[AliaNi]

Looks like you have shown new sample data, but not ..



XL2BB would also be better because then we could esily copy the data for testing.

Book1.xlsx
	A	B	C	D	E
1			Started	Ended	Scheduled
2	Scheduled Started Ended	10:00 am 10:15 am 11:00 am	10:15 AM	11:00 AM	10:00 AM
3	Started Ended	3:00 pm 4:00 pm	3:00 PM	4:00 PM
4	Started Ended Scheduled	1 pm 2 pm 12 pm	1:00 PM	2:00 PM	12:00 PM
Sheet1

 

[AliaNi]

Thanks for getting XL2BB going but you still have not included the expected result(s) and told us how you got those results manually

I just uploaded XL2BB with the expected results. I did it manually for the first 3 rows.

 

[Micron]

That is exactly what I expected as of post 4 when it was noted that text to columns would not work. Likely because not all rows are going to have all 3 values, nor would they necessarily be in the same order if they did. I can only imagine a code solution but if there's a formula for it, I know I won't understand it.