Prev: Calculating Resolution Rates
Next: Matching two sets of data?
From: Benjamin on 23 Mar 2010 18:21
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.
From: Ziggy on 23 Mar 2010 18:48
On Mar 23, 4:21 pm, Benjamin <Benja...(a)discussions.microsoft.com>
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.
From: Max on 23 Mar 2010 19:20
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.
From: Max on 24 Mar 2010 19:24
> =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
---

From: Ziggy on 25 Mar 2010 13:14
On Mar 24, 5:24 pm, Max <demecha...(a)yahoo.com> 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.
 | 
Pages: 1
Prev: Calculating Resolution Rates
Next: Matching two sets of data?