Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
I am working with a file w/ multiple sheets. The raw data is on a sheet
called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
The problem might lie with the expression of
IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,ABC ,54,FALSE)). The way the formula is written in redundant. If the result of the formula is "", then you don't need to say -then give me ""-. The VLOOKUP($A2,ABC,54,FALSE) is sufficient. It is like saying if the answer is A, then give me A otherwise give me the answer, which is A. Are you looking for an error? Next, can you tell what the value is supposed to be when it returns a zero? Make sure that the lookup value on your source data sheet is not zero. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
I am assuming ABC is a named range ...
If =VLOOKUP(A2,ABC,21,FALSE) is returning a 0 it means the 21st column in ABC for the value of A2 is blank... this means that Vlookup must be getting a match in an earlier row than the row you think it is matching... The most compelling clue is that you are still getting 0 if you delete that row... you would get a #N/A if that was the row VLOOKUP was returning the value from. To test replace the values in your 21st columns by the formula =row() this will tell you which row it is returning from... Also it will tell you that VLOOKUP is working fine... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
When I try =VLOOKUP($A2,ABC,54,FALSE) it gives the same thing. The reason I
have the IF statement is that if there is no date, I don't want a zero, just a blank. For your last question, it should be a "Y", that is the value in the source field. It seems for the two fields it can't look up the value. "PJFry" wrote: The problem might lie with the expression of IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,ABC ,54,FALSE)). The way the formula is written in redundant. If the result of the formula is "", then you don't need to say -then give me ""-. The VLOOKUP($A2,ABC,54,FALSE) is sufficient. It is like saying if the answer is A, then give me A otherwise give me the answer, which is A. Are you looking for an error? Next, can you tell what the value is supposed to be when it returns a zero? Make sure that the lookup value on your source data sheet is not zero. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
I did repllace the source field w/ =row() and it gave the correct row number.
"Sheeloo" wrote: I am assuming ABC is a named range ... If =VLOOKUP(A2,ABC,21,FALSE) is returning a 0 it means the 21st column in ABC for the value of A2 is blank... this means that Vlookup must be getting a match in an earlier row than the row you think it is matching... The most compelling clue is that you are still getting 0 if you delete that row... you would get a #N/A if that was the row VLOOKUP was returning the value from. To test replace the values in your 21st columns by the formula =row() this will tell you which row it is returning from... Also it will tell you that VLOOKUP is working fine... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
Hi
Your formula doesn't look right, in your formula, I don't see the table array or range and the reference to another is incorrect if you are placing the formula in another sheet. Something like this =VLOOKUP(A2,ABC!$A$2:$B$100,2,FALSE) This won't return a Y or N, it will return the lookup value if there matching data -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
It has to be the source page, because I noticed when referencing the raw data
from another sheet, the same thing happens. "Sheeloo" wrote: I am assuming ABC is a named range ... If =VLOOKUP(A2,ABC,21,FALSE) is returning a 0 it means the 21st column in ABC for the value of A2 is blank... this means that Vlookup must be getting a match in an earlier row than the row you think it is matching... The most compelling clue is that you are still getting 0 if you delete that row... you would get a #N/A if that was the row VLOOKUP was returning the value from. To test replace the values in your 21st columns by the formula =row() this will tell you which row it is returning from... Also it will tell you that VLOOKUP is working fine... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
Did you delete the row and get #N/A?
What does the 'bad row' have? If you replace the =row() with Y or N you should get that, right? Do you get Y or N by a formula? Can you share the file? Either email to me or upload at a site like wikesend.com and paste the link here.. "PAL" wrote: I did repllace the source field w/ =row() and it gave the correct row number. "Sheeloo" wrote: I am assuming ABC is a named range ... If =VLOOKUP(A2,ABC,21,FALSE) is returning a 0 it means the 21st column in ABC for the value of A2 is blank... this means that Vlookup must be getting a match in an earlier row than the row you think it is matching... The most compelling clue is that you are still getting 0 if you delete that row... you would get a #N/A if that was the row VLOOKUP was returning the value from. To test replace the values in your 21st columns by the formula =row() this will tell you which row it is returning from... Also it will tell you that VLOOKUP is working fine... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
The named range is ABC. It works for all rows but one.
"Francis" wrote: Hi Your formula doesn't look right, in your formula, I don't see the table array or range and the reference to another is incorrect if you are placing the formula in another sheet. Something like this =VLOOKUP(A2,ABC!$A$2:$B$100,2,FALSE) This won't return a Y or N, it will return the lookup value if there matching data -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
It has to be the source page, because I noticed when referencing the raw data
from another sheet, the same thing happens. "PJFry" wrote: The problem might lie with the expression of IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,ABC ,54,FALSE)). The way the formula is written in redundant. If the result of the formula is "", then you don't need to say -then give me ""-. The VLOOKUP($A2,ABC,54,FALSE) is sufficient. It is like saying if the answer is A, then give me A otherwise give me the answer, which is A. Are you looking for an error? Next, can you tell what the value is supposed to be when it returns a zero? Make sure that the lookup value on your source data sheet is not zero. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
On Mon, 6 Apr 2009 09:21:01 -0700, PJFry
wrote: The problem might lie with the expression of IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)). The way the formula is written in redundant. If the result of the formula is "", then you don't need to say -then give me ""-. The VLOOKUP($A2,ABC,54,FALSE) is sufficient. It is like saying if the answer is A, then give me A otherwise give me the answer, which is A. Are you looking for an error? What I noted is that both work, but yours returns a 1/01/1900 date for a blank, and hers returns a blank for a blank cell. Next, can you tell what the value is supposed to be when it returns a zero? She makes it fill a blank,it is right there in the formula. Make sure that the lookup value on your source data sheet is not zero. That isn't her problem. The data is there and valid (reference her remarks) She just has one row where the formula in that cell returns an odd, unexpected value. It has to be a cell formatting issue. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
Try hitting 'Ctrl-D' while in those non-filling cells. It should copy
the formula in the cell immediately above it, and add one count to certain cell calls. If it is wrong, you can edit it. On Mon, 6 Apr 2009 09:34:05 -0700, PAL wrote: When I try =VLOOKUP($A2,ABC,54,FALSE) it gives the same thing. The reason I have the IF statement is that if there is no date, I don't want a zero, just a blank. For your last question, it should be a "Y", that is the value in the source field. It seems for the two fields it can't look up the value. "PJFry" wrote: The problem might lie with the expression of IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,ABC ,54,FALSE)). The way the formula is written in redundant. If the result of the formula is "", then you don't need to say -then give me ""-. The VLOOKUP($A2,ABC,54,FALSE) is sufficient. It is like saying if the answer is A, then give me A otherwise give me the answer, which is A. Are you looking for an error? Next, can you tell what the value is supposed to be when it returns a zero? Make sure that the lookup value on your source data sheet is not zero. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
On Mon, 6 Apr 2009 09:36:01 -0700, PAL
wrote: I did repllace the source field w/ =row() and it gave the correct row number. Then it HAS to be cell formatting. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
On Mon, 6 Apr 2009 09:54:05 -0700, Francis
wrote: Your formula doesn't look right, in your formula, I don't see the table array or range and the reference to another is incorrect if you are placing the formula in another sheet. It is a named range, OBVIOUSLY! Use your head before you post. Try reading the whole thread even, before you step in with redundancy or stupidity. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
Can't share file.
When I delete the row with on the source sheet, the sheet stays the same, "0" when I expect "Y" or "N" and blank for a date. When I stick "=ROW()" in the two source fields, the result is the same. N or Y is comes from an export into a master file. The sheet is the copied into my file and the region of cells is named. Thanks for your patience. "Sheeloo" wrote: Did you delete the row and get #N/A? What does the 'bad row' have? If you replace the =row() with Y or N you should get that, right? Do you get Y or N by a formula? Can you share the file? Either email to me or upload at a site like wikesend.com and paste the link here.. "PAL" wrote: I did repllace the source field w/ =row() and it gave the correct row number. "Sheeloo" wrote: I am assuming ABC is a named range ... If =VLOOKUP(A2,ABC,21,FALSE) is returning a 0 it means the 21st column in ABC for the value of A2 is blank... this means that Vlookup must be getting a match in an earlier row than the row you think it is matching... The most compelling clue is that you are still getting 0 if you delete that row... you would get a #N/A if that was the row VLOOKUP was returning the value from. To test replace the values in your 21st columns by the formula =row() this will tell you which row it is returning from... Also it will tell you that VLOOKUP is working fine... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "PAL" wrote: I am working with a file w/ multiple sheets. The raw data is on a sheet called ABC which is copied from another file. One of my analysis sheets has 132 rows. This formula will return a Y or N: =VLOOKUP(A2,ABC,21,FALSE) This formula will return a date if it is not blank: =IF(VLOOKUP($A2,ABC,54,FALSE)="","",VLOOKUP($A2,AB C,54,FALSE)) I copy this formula down to the last row. It seems the data won't appear for a single row in the middle of the table. 131 rows are correct. For the bad row, I get a zero instead of Y or N and blanks for the dates. I have checked to ensure the format is right, General for the first, and a date format for the second. I have also deleted the row and tried again. I really prefer to get it autopopulated then manually put in the data. Help. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP ?
Problem solved. The raw data had two rows with the same name. Heavens to
mercatroid. Thanks for your help "Archimedes' Lever" wrote: On Mon, 6 Apr 2009 09:54:05 -0700, Francis wrote: Your formula doesn't look right, in your formula, I don't see the table array or range and the reference to another is incorrect if you are placing the formula in another sheet. It is a named range, OBVIOUSLY! Use your head before you post. Try reading the whole thread even, before you step in with redundancy or stupidity. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |