HackSlash
Active Member
- Joined
- Nov 18, 2016
- Messages
- 361
I'm not very confident with formulas. I made something really ugly and then thought "There must be a better way".
I will first tell you what I want:
I need to search for a partial string match on a cell in a known range on a different sheet. Once the match is found I need to take the cell value from below it (Row+1) and concatenate with the value of the cell below and to the right (Row+1, Col+1).
What I did:
I ended up searching for it twice using INDEX, MATCH and the "&" operator to join their values.
Can I do this with one search instead of repeating myself for the second column?
Is there an easier way? Shorter? More elegant?
I will first tell you what I want:
I need to search for a partial string match on a cell in a known range on a different sheet. Once the match is found I need to take the cell value from below it (Row+1) and concatenate with the value of the cell below and to the right (Row+1, Col+1).
What I did:
I ended up searching for it twice using INDEX, MATCH and the "&" operator to join their values.
Can I do this with one search instead of repeating myself for the second column?
Code:
=INDEX(temp.txt!$A$1:$J$22,MATCH("*| P*",temp.txt!$E$1:$E$18,0)+1,5)&INDEX(temp.txt!$A$1:$J$22,MATCH("*| P*",temp.txt!$E$1:$E$18,0)+1,6)Is there an easier way? Shorter? More elegant?
