I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

Hi,
What about somthing like:

If A2 starts with X2 then VLOOKUP(...) else A2
= IF( LEFT(A2,LEN(X2)) = LEFT(X2,LEN(X2)) , VLOOKUP(...) , A2 )

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

Thanks for the reply but nope that didn't do it. I don't think I'm
explaining myself properly...

My first sheet (sheet1) has a value in cell x32. I want to look for cells
that begin with that value in my second sheet (sheet2) in column D. Once a
value is found in column d of my second sheet i want that found value to be
copied to sheet1 cell x33.

Then keep looking for values (in sheet2 column D) that begin with the value
in sheet1 cell x32 and copy the matching results downward in cells x34, x35
etc... I guess it's sort of like and if then with a next thrown in there for
good measure.

Hope this helps make things clearer.

Thanks!

"sebastienm" wrote:

Hi,
What about somthing like:

If A2 starts with X2 then VLOOKUP(...) else A2
= IF( LEFT(A2,LEN(X2)) = LEFT(X2,LEN(X2)) , VLOOKUP(...) , A2 )

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

ok, i get it now.

Finding a single match
-------------------------
A nice feature of the VLOOKUP function is that it takes wildcard characters.
That is, in cell x33, you can write:

=VLOOKUP( x32 & "*" , sheet2!D:D, 1, False)

Notes:
- x32 & "*" means: search for values starting with value in x32 (wildcard
* = any character)
- sheet2!D:D is fixed so only the first match will be found
- if no match then #N/A is returned

Finding All Matches
---------------------

You could probably use an array formula to detrmine all matches but maybe a
better way would be to use a macro instead of a function:

Sub CopyMatches()
dim rg as range

'''' find matching cells using wildcard
set rg = FindAllBegin sheet1.range("x32").value & "*" , sheet2.range("d:D")

''' copy matching cells over
if not rg is nothing then
rg.copy sheet1.Range("x33")
end if

End sub

Function FindAll (What as Variant, Where as Range) as range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address < firstAddress
End If
End With

Set FIndAll = rgResult
end function
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

Thanks for the reply but nope that didn't do it. I don't think I'm
explaining myself properly...

My first sheet (sheet1) has a value in cell x32. I want to look for cells
that begin with that value in my second sheet (sheet2) in column D. Once a
value is found in column d of my second sheet i want that found value to be
copied to sheet1 cell x33.

Then keep looking for values (in sheet2 column D) that begin with the value
in sheet1 cell x32 and copy the matching results downward in cells x34, x35
etc... I guess it's sort of like and if then with a next thrown in there for
good measure.

Hope this helps make things clearer.

Thanks!

"sebastienm" wrote:

Hi,
What about somthing like:

If A2 starts with X2 then VLOOKUP(...) else A2
= IF( LEFT(A2,LEN(X2)) = LEFT(X2,LEN(X2)) , VLOOKUP(...) , A2 )

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

I see where this is going but I'm getting an error when I'm getting a syntax
error when I tweek it for my specifics... Your ogirnal code has the sub
reference the function of FindAllBegin where the Function is named FindAll,
which is why I changed that thinking that was the problem but it did not fix
it. Here is my modification...

Sub CopyMatches()
Dim rg As Range

'''' find matching cells using wildcard
Set rg = FindAll January.Range("X31").Value & "*",
weights.xls!CustomerAccounts.Range("D:D")

''' copy matching cells over
If Not rg Is Nothing Then
rg.Copy January.Range("X33")
End If

End Sub

Function FindAll(What As Variant, Where As Range) As Range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address < firstAddress
End If
End With

Set FindAll = rgResult
End Function


"sebastienm" wrote:

ok, i get it now.

Finding a single match
-------------------------
A nice feature of the VLOOKUP function is that it takes wildcard characters.
That is, in cell x33, you can write:

=VLOOKUP( x32 & "*" , sheet2!D:D, 1, False)

Notes:
- x32 & "*" means: search for values starting with value in x32 (wildcard
* = any character)
- sheet2!D:D is fixed so only the first match will be found
- if no match then #N/A is returned

Finding All Matches
---------------------

You could probably use an array formula to detrmine all matches but maybe a
better way would be to use a macro instead of a function:

Sub CopyMatches()
dim rg as range

'''' find matching cells using wildcard
set rg = FindAllBegin sheet1.range("x32").value & "*" , sheet2.range("d:D")

''' copy matching cells over
if not rg is nothing then
rg.copy sheet1.Range("x33")
end if

End sub

Function FindAll (What as Variant, Where as Range) as range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address < firstAddress
End If
End With

Set FIndAll = rgResult
end function
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

Thanks for the reply but nope that didn't do it. I don't think I'm
explaining myself properly...

My first sheet (sheet1) has a value in cell x32. I want to look for cells
that begin with that value in my second sheet (sheet2) in column D. Once a
value is found in column d of my second sheet i want that found value to be
copied to sheet1 cell x33.

Then keep looking for values (in sheet2 column D) that begin with the value
in sheet1 cell x32 and copy the matching results downward in cells x34, x35
etc... I guess it's sort of like and if then with a next thrown in there for
good measure.

Hope this helps make things clearer.

Thanks!

"sebastienm" wrote:

Hi,
What about somthing like:

If A2 starts with X2 then VLOOKUP(...) else A2
= IF( LEFT(A2,LEN(X2)) = LEFT(X2,LEN(X2)) , VLOOKUP(...) , A2 )

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Stephen" wrote:

I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

change
Set rg = FindAll( January.Range("X31").Value & "*", weights.xls!
CustomerAccounts.Range ("D:D")
with
''' ----------------------------------------
Set rg = FindAll( _
ThisWorkbook.Worksheets("January").Range("X31").Va lue & "*" _
, Workbooks("weights.xls").Worksheets("CustomerAccou nts").Range("D:D") _
)
''' ----------------------------------------

--
Regards,
Sébastien
<http://www.ondemandanalysis.com