| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem is killin me.
i have a data base on sheet 1, formula is on sheet 2. its sorted ascending
everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
Are you using all 4 arguments of the function?
Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
=VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE)
=VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
If you're looking for an exact match
=VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
same thing. I'm sorry, but ..... don't know what else to do.
"daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
It sounds like you have a data problem.
Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
values are imported from another software type. numbers are stored as text,
i have combined 2 columns (first & last names) to read as one variable. Is there a way that i can "lookup" an ID # based on multiple cells to avoid any problems from combining them? BTW- same resukt from index formula? Thanks a bunch for tryin to help a greenhorn. "RagDyer" wrote: It sounds like you have a data problem. Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
We're now 7 posts deep into this thread, and just *now* we learn of text
numbers and combined (exactly what that means ... I don't know) columns. SO ... to help, we need an understandable description of your datalist, the one that the formula will perform computations on. What type of data is in each *pertinent* (what the formula will reference) column, and what type of data is in the column used as the lookup value that the formula will poll. Being thorough and still concise might be considered an art. This link I garnered from Jim Cone is *very* apropos: http://www.catb.org/~esr/faqs/smart-questions.html The people around here enjoy solving problems and helping. We're all very selfish! We race and trip over each other in order to get you the help you want. Give us the ammo (information) so that we can bask in our own enjoyment of helping you. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "seeinred04" wrote in message ... values are imported from another software type. numbers are stored as text, i have combined 2 columns (first & last names) to read as one variable. Is there a way that i can "lookup" an ID # based on multiple cells to avoid any problems from combining them? BTW- same resukt from index formula? Thanks a bunch for tryin to help a greenhorn. "RagDyer" wrote: It sounds like you have a data problem. Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
First off, Im sorry for the confusion. I am under a time crunch, and
figured that it was probably a stupid mistake on my part that would be easily resolved. Its still gonna be a stupid mistake on my part but€¦ I am new to posting questions and I am not for sure what may or may not be pertinent information. That being said, here we go. My data is exported from a Sage Software. Basically it is detailed directory of people that meet certain criteria. Some meet more than one so they are listed more than once. The data contains an ID number, last name, first name, Address, etc. I have another list that was exported from InfoPoint. It has basically the same info EXCEPT, it has an Account Number instead of ID and only includes unique records. I need both the Account and ID numbers in one spreadsheet. The only thing that I knew to reference is full name, which is in 2 separate columns. So I combined the 2 columns by way of: =I2&" "&J2 for both lists. Then on one of the lists I am adding a column with the lookup formula to lookup the ID number based on name. I have tried: =VLOOKUP(H2,Sheet1!A1:G19677,5) =VLOOKUP(H2,Sheet1!A1:G19677,5,0) =VLOOKUP(H2,Sheet1!A1:G19677,5,1) =VLOOKUP(H2,Sheet1!A1:G19677,5,FALSE) =VLOOKUP(H2,Sheet1!A1:G19677,5,TRUE) =INDEX(Sheet1!F2:F19677,MATCH(H3,Sheet1!B2:B19677, 1)) All of which result in either #N/A or the ID number of the person in the row just above who I need. I hope this is clear and not too long but Im simple minded and had to explain it to myself several times during this explanation. Thanks. "RagDyeR" wrote: We're now 7 posts deep into this thread, and just *now* we learn of text numbers and combined (exactly what that means ... I don't know) columns. SO ... to help, we need an understandable description of your datalist, the one that the formula will perform computations on. What type of data is in each *pertinent* (what the formula will reference) column, and what type of data is in the column used as the lookup value that the formula will poll. Being thorough and still concise might be considered an art. This link I garnered from Jim Cone is *very* apropos: http://www.catb.org/~esr/faqs/smart-questions.html The people around here enjoy solving problems and helping. We're all very selfish! We race and trip over each other in order to get you the help you want. Give us the ammo (information) so that we can bask in our own enjoyment of helping you. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "seeinred04" wrote in message ... values are imported from another software type. numbers are stored as text, i have combined 2 columns (first & last names) to read as one variable. Is there a way that i can "lookup" an ID # based on multiple cells to avoid any problems from combining them? BTW- same resukt from index formula? Thanks a bunch for tryin to help a greenhorn. "RagDyer" wrote: It sounds like you have a data problem. Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
Hi
Could it be that in H2 you have I1 &" "&J1 instead of I2&" "&J2? -- Regards Roger Govier "seeinred04" wrote in message ... First off, I'm sorry for the confusion. I am under a time crunch, and figured that it was probably a stupid mistake on my part that would be easily resolved. It's still gonna be a stupid mistake on my part but. I am new to posting questions and I am not for sure what may or may not be pertinent information. That being said, here we go. My data is exported from a Sage Software. Basically it is detailed directory of people that meet certain criteria. Some meet more than one so they are listed more than once. The data contains an ID number, last name, first name, Address, etc. I have another list that was exported from InfoPoint. It has basically the same info EXCEPT, it has an Account Number instead of ID and only includes unique records. I need both the Account and ID numbers in one spreadsheet. The only thing that I knew to reference is full name, which is in 2 separate columns. So I combined the 2 columns by way of: =I2&" "&J2 for both lists. Then on one of the lists I am adding a column with the lookup formula to lookup the ID number based on name. I have tried: =VLOOKUP(H2,Sheet1!A1:G19677,5) =VLOOKUP(H2,Sheet1!A1:G19677,5,0) =VLOOKUP(H2,Sheet1!A1:G19677,5,1) =VLOOKUP(H2,Sheet1!A1:G19677,5,FALSE) =VLOOKUP(H2,Sheet1!A1:G19677,5,TRUE) =INDEX(Sheet1!F2:F19677,MATCH(H3,Sheet1!B2:B19677, 1)) All of which result in either #N/A or the ID number of the person in the row just above who I need. I hope this is clear and not too long but I'm simple minded and had to explain it to myself several times during this explanation. Thanks. "RagDyeR" wrote: We're now 7 posts deep into this thread, and just *now* we learn of text numbers and combined (exactly what that means ... I don't know) columns. SO ... to help, we need an understandable description of your datalist, the one that the formula will perform computations on. What type of data is in each *pertinent* (what the formula will reference) column, and what type of data is in the column used as the lookup value that the formula will poll. Being thorough and still concise might be considered an art. This link I garnered from Jim Cone is *very* apropos: http://www.catb.org/~esr/faqs/smart-questions.html The people around here enjoy solving problems and helping. We're all very selfish! We race and trip over each other in order to get you the help you want. Give us the ammo (information) so that we can bask in our own enjoyment of helping you. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "seeinred04" wrote in message ... values are imported from another software type. numbers are stored as text, i have combined 2 columns (first & last names) to read as one variable. Is there a way that i can "lookup" an ID # based on multiple cells to avoid any problems from combining them? BTW- same resukt from index formula? Thanks a bunch for tryin to help a greenhorn. "RagDyer" wrote: It sounds like you have a data problem. Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup problem is killin me.
Whenever the word "imported" is mentioned, there is always the possibility
of additional, invisible characters being included within that imported data. I would start off by checking the length of the I and J columns using, =Len(I2) =Len(J2) And comparing the results to a visual count. Do they all match? BTW ... when you receive a return of the name from the row above the correct name, does the formula you used call for approximate matches (ends in 1 or TRUE)? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "seeinred04" wrote in message ... First off, I'm sorry for the confusion. I am under a time crunch, and figured that it was probably a stupid mistake on my part that would be easily resolved. It's still gonna be a stupid mistake on my part but. I am new to posting questions and I am not for sure what may or may not be pertinent information. That being said, here we go. My data is exported from a Sage Software. Basically it is detailed directory of people that meet certain criteria. Some meet more than one so they are listed more than once. The data contains an ID number, last name, first name, Address, etc. I have another list that was exported from InfoPoint. It has basically the same info EXCEPT, it has an Account Number instead of ID and only includes unique records. I need both the Account and ID numbers in one spreadsheet. The only thing that I knew to reference is full name, which is in 2 separate columns. So I combined the 2 columns by way of: =I2&" "&J2 for both lists. Then on one of the lists I am adding a column with the lookup formula to lookup the ID number based on name. I have tried: =VLOOKUP(H2,Sheet1!A1:G19677,5) =VLOOKUP(H2,Sheet1!A1:G19677,5,0) =VLOOKUP(H2,Sheet1!A1:G19677,5,1) =VLOOKUP(H2,Sheet1!A1:G19677,5,FALSE) =VLOOKUP(H2,Sheet1!A1:G19677,5,TRUE) =INDEX(Sheet1!F2:F19677,MATCH(H3,Sheet1!B2:B19677, 1)) All of which result in either #N/A or the ID number of the person in the row just above who I need. I hope this is clear and not too long but I'm simple minded and had to explain it to myself several times during this explanation. Thanks. "RagDyeR" wrote: We're now 7 posts deep into this thread, and just *now* we learn of text numbers and combined (exactly what that means ... I don't know) columns. SO ... to help, we need an understandable description of your datalist, the one that the formula will perform computations on. What type of data is in each *pertinent* (what the formula will reference) column, and what type of data is in the column used as the lookup value that the formula will poll. Being thorough and still concise might be considered an art. This link I garnered from Jim Cone is *very* apropos: http://www.catb.org/~esr/faqs/smart-questions.html The people around here enjoy solving problems and helping. We're all very selfish! We race and trip over each other in order to get you the help you want. Give us the ammo (information) so that we can bask in our own enjoyment of helping you. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "seeinred04" wrote in message ... values are imported from another software type. numbers are stored as text, i have combined 2 columns (first & last names) to read as one variable. Is there a way that i can "lookup" an ID # based on multiple cells to avoid any problems from combining them? BTW- same resukt from index formula? Thanks a bunch for tryin to help a greenhorn. "RagDyer" wrote: It sounds like you have a data problem. Are your values imported, returns from other formulas, or keyed in? BTW ... What happens with this formula: =Index(Sheet1!E1:E19677,Match(G2,Sheet1!A1:A19677, 0)) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... same thing. I'm sorry, but ..... don't know what else to do. "daddylonglegs" wrote: If you're looking for an exact match =VLOOKUP(G2,Sheet1!A1:F19677,5,0) no sorting required "seeinred04" wrote: =VLOOKUP(G2,Sheet1!A1:F19677,5,TRUE) =VLOOKUP(G2,Sheet1!A1:F19677,5) i've tried both and they both return value of the cell above the correct one. "RagDyer" wrote: Are you using all 4 arguments of the function? Is the 4th argument 0 or "False", denoting that an exact match is called for? If "yes" to the above, post the formula you're using. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "seeinred04" wrote in message ... i have a data base on sheet 1, formula is on sheet 2. its sorted ascending everything looks right, but it returns the value from the correct column but always picks from the previous row. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup problem | Excel Discussion (Misc queries) | |||
| Vlookup problem with Date Time | Excel Worksheet Functions | |||
| vlookup Problem | Excel Discussion (Misc queries) | |||
| VLOOKUP Problem | Excel Worksheet Functions | |||
| VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions | |||
Linear Mode
