VLOOKUP ?

[PAL]

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,ABC,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.

 

[PJFry]

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.

 

[Sheeloo]

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

 

[PAL]

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.

 

[PAL]

I did repllace the source field w/ =row() and it gave the correct row number.

 

[Francis]

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]

It has to be the source page, because I noticed when referencing the raw data
from another sheet, the same thing happens.

 

[Sheeloo]

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]

The named range is ABC. It works for all rows but one.

 

[PAL]

It has to be the source page, because I noticed when referencing the raw data
from another sheet, the same thing happens.

 

[Archimedes' Lever]

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,ABC,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.

When you "copy from another sheet" what are the formats of those cells?

Also, if any data in those cells is pasted or looked up from yet
another location, be sure to carry the proper cell formatting though.

 

[Archimedes' Lever]

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?

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.

 

[Archimedes' Lever]

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.

 

[Archimedes' Lever]

I did repllace the source field w/ =row() and it gave the correct row number.

Then it HAS to be cell formatting.

 

[Archimedes' Lever]

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.

 

[PAL]

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.

 

[PAL]

Problem solved. The raw data had two rows with the same name. Heavens to
mercatroid. Thanks for your help