LOOKUP - Driving me potty!!!

[wbiggchiefy]

Pls Help

Lookup function is driving me mad -

I am trying to get LOOKUP function to return a value from cell, say column C
on a worksheet but the error is erroneous (it seems to be returning a value
attached to another entry on the worksheet with the same first 4 letters)

Is there some confine within excel which only 'looks up' the first 4 letters
of a name or am I missing something?

Also sometimes I get values returned to the formula I have used which seem
to be 4-5 rows out of sync.

I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
DETAILS'!$C$2:$C$1000)

Thanks in advance.

Chiefy.

[wbiggchiefy]

"wbiggchiefy" wrote:

> Pls Help
>
> Lookup function is driving me mad -
>
> I am trying to get LOOKUP function to return a value from cell, say column C
> on a worksheet but the error is erroneous (it seems to be returning a value
> attached to another entry on the worksheet with the same first 4 letters)
>
> Is there some confine within excel which only 'looks up' the first 4 letters
> of a name or am I missing something?
>
> Also sometimes I get values returned to the formula I have used which seem
> to be 4-5 rows out of sync.
>
> I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> DETAILS'!$C$2:$C$1000)
>
> Thanks in advance.
>
> Chiefy.


Also have just noticed that if I enter the full name that is in the
worksheet I am pulling the info from that the formula works - but would like
to not have to input the full name (first 8-10 characters should be enough).

Chiefy.

[Aladin Akyurek]

Since you want to work with a abbreviated name as lookup value...

=LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> DETAILS'!$C$2:$C$1000)

=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649&"*",'CIS
DETAILS'!$A$2:$A$1000,0))

wbiggchiefy wrote:
>
> "wbiggchiefy" wrote:
>
>
>>Pls Help
>>
>>Lookup function is driving me mad -
>>
>>I am trying to get LOOKUP function to return a value from cell, say column C
>>on a worksheet but the error is erroneous (it seems to be returning a value
>>attached to another entry on the worksheet with the same first 4 letters)
>>
>>Is there some confine within excel which only 'looks up' the first 4 letters
>>of a name or am I missing something?
>>
>>Also sometimes I get values returned to the formula I have used which seem
>>to be 4-5 rows out of sync.
>>
>>I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
>>DETAILS'!$C$2:$C$1000)
>>
>>Thanks in advance.
>>
>>Chiefy.
>
>
>
> Also have just noticed that if I enter the full name that is in the
> worksheet I am pulling the info from that the formula works - but would like
> to not have to input the full name (first 8-10 characters should be enough).
>
> Chiefy.

[Dave Peterson]

Does this mean you want an exact match in A2:A1000?

If you do, then maybe =vlookup() is a better choice:

=LOOKUP(C1649,'CIS DETAILS'!$A$2:$c$1000,3,false)

The value to match up is in C1649
The range is 3 columns (A:C on cis details) wide.
The column I want to bring back is the 3rd column in that range.
The false means that I want an exact match (an error will be returned if there
is no match)

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html


wbiggchiefy wrote:
>
> Pls Help
>
> Lookup function is driving me mad -
>
> I am trying to get LOOKUP function to return a value from cell, say column C
> on a worksheet but the error is erroneous (it seems to be returning a value
> attached to another entry on the worksheet with the same first 4 letters)
>
> Is there some confine within excel which only 'looks up' the first 4 letters
> of a name or am I missing something?
>
> Also sometimes I get values returned to the formula I have used which seem
> to be 4-5 rows out of sync.
>
> I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> DETAILS'!$C$2:$C$1000)
>
> Thanks in advance.
>
> Chiefy.

--

Dave Peterson

[Dave Peterson]

Another typo!

=LOOKUP(C1649,'CIS DETAILS'!$A$2:$c$1000,3,false)
should have been:
=VLOOKUP(C1649,'CIS DETAILS'!$A$2:$c$1000,3,false)

=vlookup()



Dave Peterson wrote:
>
> Does this mean you want an exact match in A2:A1000?
>
> If you do, then maybe =vlookup() is a better choice:
>
> =LOOKUP(C1649,'CIS DETAILS'!$A$2:$c$1000,3,false)
>
> The value to match up is in C1649
> The range is 3 columns (A:C on cis details) wide.
> The column I want to bring back is the 3rd column in that range.
> The false means that I want an exact match (an error will be returned if there
> is no match)
>
> You may want to read Debra Dalgleish's notes:
> http://www.contextures.com/xlFunctions02.html
>
> wbiggchiefy wrote:
> >
> > Pls Help
> >
> > Lookup function is driving me mad -
> >
> > I am trying to get LOOKUP function to return a value from cell, say column C
> > on a worksheet but the error is erroneous (it seems to be returning a value
> > attached to another entry on the worksheet with the same first 4 letters)
> >
> > Is there some confine within excel which only 'looks up' the first 4 letters
> > of a name or am I missing something?
> >
> > Also sometimes I get values returned to the formula I have used which seem
> > to be 4-5 rows out of sync.
> >
> > I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> > DETAILS'!$C$2:$C$1000)
> >
> > Thanks in advance.
> >
> > Chiefy.
>
> --
>
> Dave Peterson

--

Dave Peterson

[Aladin Akyurek]

If A:C on CIS DETAILS is sorted in ascending order on A...

=IF(LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000)=C1649,LOOKUP(C1649,'CIS
DETAILS'!$A$2:$A$1000,'CIS DETAILS'!$C$2:$C$1000),"Not Found")

If unsorted...

=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649,'CIS DETAILS'!$A$2:$A$1000,0))

wbiggchiefy wrote:
> Pls Help
>
> Lookup function is driving me mad -
>
> I am trying to get LOOKUP function to return a value from cell, say column C
> on a worksheet but the error is erroneous (it seems to be returning a value
> attached to another entry on the worksheet with the same first 4 letters)
>
> Is there some confine within excel which only 'looks up' the first 4 letters
> of a name or am I missing something?
>
> Also sometimes I get values returned to the formula I have used which seem
> to be 4-5 rows out of sync.
>
> I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> DETAILS'!$C$2:$C$1000)
>
> Thanks in advance.
>
> Chiefy.

[wbiggchiefy]

"Aladin Akyurek" wrote:

> If A:C on CIS DETAILS is sorted in ascending order on A...
>
> =IF(LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000)=C1649,LOOKUP(C1649,'CIS
> DETAILS'!$A$2:$A$1000,'CIS DETAILS'!$C$2:$C$1000),"Not Found")
>
> If unsorted...
>
> =INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649,'CIS DETAILS'!$A$2:$A$1000,0))
>
> wbiggchiefy wrote:
> > Pls Help
> >
> > Lookup function is driving me mad -
> >
> > I am trying to get LOOKUP function to return a value from cell, say column C
> > on a worksheet but the error is erroneous (it seems to be returning a value
> > attached to another entry on the worksheet with the same first 4 letters)
> >
> > Is there some confine within excel which only 'looks up' the first 4 letters
> > of a name or am I missing something?
> >
> > Also sometimes I get values returned to the formula I have used which seem
> > to be 4-5 rows out of sync.
> >
> > I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> > DETAILS'!$C$2:$C$1000)
> >
> > Thanks in advance.
> >
> > Chiefy.
>
Sorry guys - Alvin seems to be the closest and my formula works fine as long
as I match exactly with 2nd sheet - if I don't I get errors - matches with
1st alphabetiacally of 1st few characters!

Can I make formula matdh to first 8 characters as it is in a way linked to
the monstrosity that is sage (sorry to swear but I have 2 work with it) ?

PS I hate SAGE it is the most monsterously overpriced pile anywhere.... and
hate to say it but it is easy to crack but now I'm just gettin personal ...
Hm.Hm.Hm.

SAGE- HATE EM - MORE - THAN - SCOUSERS - & MAN UTD - See I'm not bitter
- really!

Chiefy.

[Aladin Akyurek]

If C1649 houses the first 4 chars of interest...

=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649&"*",'CIS
DETAILS'!$A$2:$A$1000,0))

wbiggchiefy wrote:
>
> "Aladin Akyurek" wrote:
>
>
>>If A:C on CIS DETAILS is sorted in ascending order on A...
>>
>>=IF(LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000)=C1649,LOOKUP(C1649,'CIS
>>DETAILS'!$A$2:$A$1000,'CIS DETAILS'!$C$2:$C$1000),"Not Found")
>>
>>If unsorted...
>>
>>=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649,'CIS DETAILS'!$A$2:$A$1000,0))
>>
>>wbiggchiefy wrote:
>>
>>>Pls Help
>>>
>>>Lookup function is driving me mad -
>>>
>>>I am trying to get LOOKUP function to return a value from cell, say column C
>>>on a worksheet but the error is erroneous (it seems to be returning a value
>>>attached to another entry on the worksheet with the same first 4 letters)
>>>
>>>Is there some confine within excel which only 'looks up' the first 4 letters
>>>of a name or am I missing something?
>>>
>>>Also sometimes I get values returned to the formula I have used which seem
>>>to be 4-5 rows out of sync.
>>>
>>>I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
>>>DETAILS'!$C$2:$C$1000)
>>>
>>>Thanks in advance.
>>>
>>>Chiefy.
>>
> Sorry guys - Alvin seems to be the closest and my formula works fine as long
> as I match exactly with 2nd sheet - if I don't I get errors - matches with
> 1st alphabetiacally of 1st few characters!
>
> Can I make formula matdh to first 8 characters as it is in a way linked to
> the monstrosity that is sage (sorry to swear but I have 2 work with it) ?
>
> PS I hate SAGE it is the most monsterously overpriced pile anywhere.... and
> hate to say it but it is easy to crack but now I'm just gettin personal ...
> Hm.Hm.Hm.
>
> SAGE- HATE EM - MORE - THAN - SCOUSERS - & MAN UTD - See I'm not bitter
> - really!
>
> Chiefy.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

[wbiggchiefy]

Aladin - sorry been offline most of the day - u know the kids want 2 play
their games on my pc & laptop so I am relegated to washing up the dishes!

In reference to your reply -

C1649 houses a number (cis certificate number) which I want to show in my
other worksheet when I run the formula.

The characters which define the selection are housed in Column A on the CIS
Details Sheet and Column B on my 'SCDB' Sheet (the sheet where I want the
data to appear)

As said before my formula works fine as long as there is an exact match -
but as different people have been involved in setting up the spreadsheets &
data base things don't always match exactly - so would like to structure
formula based on the first 15 charactors (cell contains company names - some
quite long & many similar e.g Constrution Poodles Ltd - Construction Doodles
Ltd)

Forgive my ignorance as this is probably easy for you , but I have tried to
suss this out myself - to no avail.

Chiefy.

"Aladin Akyurek" wrote:

> If C1649 houses the first 4 chars of interest...
>
> =INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649&"*",'CIS
> DETAILS'!$A$2:$A$1000,0))
>
> wbiggchiefy wrote:
> >
> > "Aladin Akyurek" wrote:
> >
> >
> >>If A:C on CIS DETAILS is sorted in ascending order on A...
> >>
> >>=IF(LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000)=C1649,LOOKUP(C1649,'CIS
> >>DETAILS'!$A$2:$A$1000,'CIS DETAILS'!$C$2:$C$1000),"Not Found")
> >>
> >>If unsorted...
> >>
> >>=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649,'CIS DETAILS'!$A$2:$A$1000,0))
> >>
> >>wbiggchiefy wrote:
> >>
> >>>Pls Help
> >>>
> >>>Lookup function is driving me mad -
> >>>
> >>>I am trying to get LOOKUP function to return a value from cell, say column C
> >>>on a worksheet but the error is erroneous (it seems to be returning a value
> >>>attached to another entry on the worksheet with the same first 4 letters)
> >>>
> >>>Is there some confine within excel which only 'looks up' the first 4 letters
> >>>of a name or am I missing something?
> >>>
> >>>Also sometimes I get values returned to the formula I have used which seem
> >>>to be 4-5 rows out of sync.
> >>>
> >>>I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
> >>>DETAILS'!$C$2:$C$1000)
> >>>
> >>>Thanks in advance.
> >>>
> >>>Chiefy.
> >>
> > Sorry guys - Alvin seems to be the closest and my formula works fine as long
> > as I match exactly with 2nd sheet - if I don't I get errors - matches with
> > 1st alphabetiacally of 1st few characters!
> >
> > Can I make formula matdh to first 8 characters as it is in a way linked to
> > the monstrosity that is sage (sorry to swear but I have 2 work with it) ?
> >
> > PS I hate SAGE it is the most monsterously overpriced pile anywhere.... and
> > hate to say it but it is easy to crack but now I'm just gettin personal ...
> > Hm.Hm.Hm.
> >
> > SAGE- HATE EM - MORE - THAN - SCOUSERS - & MAN UTD - See I'm not bitter
> > - really!
> >
> > Chiefy.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>

[Aladin Akyurek]

Have a look at this fuzzy match code:

http://www.mrexcel.com/board2/viewtopic.php?t=72280

wbiggchiefy wrote:
> Aladin - sorry been offline most of the day - u know the kids want 2 play
> their games on my pc & laptop so I am relegated to washing up the dishes!
>
> In reference to your reply -
>
> C1649 houses a number (cis certificate number) which I want to show in my
> other worksheet when I run the formula.
>
> The characters which define the selection are housed in Column A on the CIS
> Details Sheet and Column B on my 'SCDB' Sheet (the sheet where I want the
> data to appear)
>
> As said before my formula works fine as long as there is an exact match -
> but as different people have been involved in setting up the spreadsheets &
> data base things don't always match exactly - so would like to structure
> formula based on the first 15 charactors (cell contains company names - some
> quite long & many similar e.g Constrution Poodles Ltd - Construction Doodles
> Ltd)
>
> Forgive my ignorance as this is probably easy for you , but I have tried to
> suss this out myself - to no avail.
>
> Chiefy.
>
> "Aladin Akyurek" wrote:
>
>
>>If C1649 houses the first 4 chars of interest...
>>
>>=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649&"*",'CIS
>>DETAILS'!$A$2:$A$1000,0))
>>
>>wbiggchiefy wrote:
>>
>>>"Aladin Akyurek" wrote:
>>>
>>>
>>>
>>>>If A:C on CIS DETAILS is sorted in ascending order on A...
>>>>
>>>>=IF(LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000)=C1649,LOOKUP(C1649,'CIS
>>>>DETAILS'!$A$2:$A$1000,'CIS DETAILS'!$C$2:$C$1000),"Not Found")
>>>>
>>>>If unsorted...
>>>>
>>>>=INDEX('CIS DETAILS'!$C$2:$C$1000,MATCH(C1649,'CIS DETAILS'!$A$2:$A$1000,0))
>>>>
>>>>wbiggchiefy wrote:
>>>>
>>>>
>>>>>Pls Help
>>>>>
>>>>>Lookup function is driving me mad -
>>>>>
>>>>>I am trying to get LOOKUP function to return a value from cell, say column C
>>>>>on a worksheet but the error is erroneous (it seems to be returning a value
>>>>>attached to another entry on the worksheet with the same first 4 letters)
>>>>>
>>>>>Is there some confine within excel which only 'looks up' the first 4 letters
>>>>>of a name or am I missing something?
>>>>>
>>>>>Also sometimes I get values returned to the formula I have used which seem
>>>>>to be 4-5 rows out of sync.
>>>>>
>>>>>I am using formula =LOOKUP(C1649,'CIS DETAILS'!$A$2:$A$1000,'CIS
>>>>>DETAILS'!$C$2:$C$1000)
>>>>>
>>>>>Thanks in advance.
>>>>>
>>>>>Chiefy.
>>>>
>>>Sorry guys - Alvin seems to be the closest and my formula works fine as long
>>>as I match exactly with 2nd sheet - if I don't I get errors - matches with
>>>1st alphabetiacally of 1st few characters!
>>>
>>>Can I make formula matdh to first 8 characters as it is in a way linked to
>>>the monstrosity that is sage (sorry to swear but I have 2 work with it) ?
>>>
>>>PS I hate SAGE it is the most monsterously overpriced pile anywhere.... and
>>>hate to say it but it is easy to crack but now I'm just gettin personal ...
>>>Hm.Hm.Hm.
>>>
>>>SAGE- HATE EM - MORE - THAN - SCOUSERS - & MAN UTD - See I'm not bitter
>>>- really!
>>>
>>>Chiefy.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>