If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLookup with Search for in String
I need to look for this value within a cell
i.e. A1XT-4-0713-01-00021-1.PDF within Folder:Folder:Folder:Folder:FolderPath:A1XT-4-0713-01-00021-1.PDF then return that row's third column's value. |
#2
|
|||
|
|||
VLookup with Search for in String
On Mar 23, 4:21*pm, Benjamin
wrote: I need to look for this value within a cell i.e. A1XT-4-0713-01-00021-1.PDF within Folder:Folder:Folder:Folder:FolderPath:A1XT-4-0713-01-00021-1.PDF *then return that row's third column's value. =SUMIF(A2,"*"&"A1XT-4-0713-01-00021-1.PDF"&"*",C2) IF A2 contains the string it will pick up the value in column C. This is intended for a single row,,, to be copied vertically to the extent of the database. |
#3
|
|||
|
|||
VLookup with Search for in String
You can work it this way for fuzzy searches ...
Assume your source data in cols A and C from row2 down Assume A1 contains the string: A1XT-4-0713-01-00021-1.PDF Put this in B1, press normal ENTER to confirm will do: =INDEX(C2:C100,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1 ,A2:A100)),),0)) B1 will return the required result from col C. Inspiring? hit the YES below -- Max Singapore --- "Benjamin" wrote: I need to look for this value within a cell i.e. A1XT-4-0713-01-00021-1.PDF within Folder:Folder:Folder:Folder:FolderPath:A1XT-4-0713-01-00021-1.PDF then return that row's third column's value. |
#4
|
|||
|
|||
VLookup with Search for in String
=SUMIF(A2,"*"&"A1XT-4-0713-01-00021-1.PDF"&"*",C2)
IF A2 contains the string it will pick up the value in column C But it fails if col C = text. I'd prefer the more generic index/match way explained in my response which works to return both text or numbers. You had another earlier posting which bears relevance to this issue. If you use the more generic index/match instead of SUM conditional functions to perform lookup-n-return, then it doesn't matter what the return col may contain (text, nums or mixed data). It'll work right through. -- Max Singapore --- |
#5
|
|||
|
|||
VLookup with Search for in String
On Mar 24, 5:24*pm, Max wrote:
=SUMIF(A2,"*"&"A1XT-4-0713-01-00021-1.PDF"&"*",C2) IF A2 contains the string it will pick up the value in column C But it fails if col C = text. I'd prefer the more generic index/match way explained in my response which works to return both text or numbers. You had another earlier posting which bears relevance to this issue. If you use the more generic index/match instead of SUM conditional functions to perform lookup-n-return, then it doesn't matter what the return col may contain (text, nums or mixed data). It'll work right through. -- Max Singapore --- True. Where I use this the "C" always contains a value. Very good point though. |
Thread Tools | |
Display Modes | |
|
|