|
LOOKUP - Driving me potty!!!
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. |
LOOKUP - Driving me potty!!!
"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. |
LOOKUP - Driving me potty!!!
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 |
LOOKUP - Driving me potty!!!
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. |
LOOKUP - Driving me potty!!!
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 |
LOOKUP - Driving me potty!!!
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. |
LOOKUP - Driving me potty!!!
"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. |
LOOKUP - Driving me potty!!!
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. |
LOOKUP - Driving me potty!!!
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. |
LOOKUP - Driving me potty!!!
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. |
| All times are GMT +1. The time now is 11:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com