LOOKUP - Driving me potty!!!

W

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.
 
W

wbiggchiefy

wbiggchiefy said:
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.
Click to expand...


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.
 
D

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
 
A

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))
 
D

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()
 
A

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)
Click to expand...

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

wbiggchiefy

Aladin Akyurek said:
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))
Click to expand...
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.
 
A

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))
 
W

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.
 
Top