Forum Discussion
Search find in excel
This may be a basic question, but I have been looking for the way to find an answer for this over the past few days.
Suppose sheet 1, ColumnA:
123
456
789
Then I have another sheet2, columnA
1724141
3415123
5123456
3123451
5123789
Is there a way for me to populate in columnB from sheet2 if the substring is found? my expectation is
columnA columnB
123 3415123 (columnB contains '123')
456 5123456 (columnB contains '456')
789 5123789 (columnB contains '789')
Thanks
Twifoo , at least that's my understanding of the question. In this its' part.
8 Replies
- You may try this formula in Sheet2!B1, copied down rows:
=INDEX(A:A,
MATCH(“*”&Sheet1!A1&”*”,
A:A,0)
The foregoing formula returns the first value in Column A that contains the value in Sheet1!A1.Twifoo , the only point the formula shall return result into column B of Sheet1 from A:A in Sheet2.
- Perhaps, I misunderstood the requirement. The formula should be in Sheet1!B1, copied down rows, which is:
=INDEX(Sheet2!A:A,
MATCH(“*”&A1&”*”,
Sheet2!A:A,0))
hnandarusdy , substring 123 in all of
3415123
5123456
3123451
5123789
You'd like to compare only last characters, or return first found or what?
