Vlookup through entire column

T

Tina M

I have a vlookup formula put in place to look at column M (a year), fin
the matching year in column A on a different tab and return the value i
column B =VLOOKUP(M6,'ENR Index'!A:B,2)
Very simple formula.

The problem is - after the 6th line on the worksheet, the formul
returns a value of N/A.
I have sorted the sheet a number of different ways, but continue t
receive the same fomula in the 6th line.
I have entered False and True in the the formula but still come up wit
nothing.


Can anyone assist with this?
Thank yo
 
B

BenjieLop

Tina said:
I have a vlookup formula put in place to look at column M (a year), find
the matching year in column A on a different tab and return the value in
column B =VLOOKUP(M6,'ENR Index'!A:B,2)
Very simple formula.

The problem is - after the 6th line on the worksheet, the formula
returns a value of N/A.
I have sorted the sheet a number of different ways, but continue to
receive the same fomula in the 6th line.
I have entered False and True in the the formula but still come up with
nothing.


Can anyone assist with this?
Thank you
Click to expand...


Assuming your range is A1:B100, maybe this will help ...

=VLOOKUP(M6,'ENR Index'!*$*A*$*1:*$*B*$*100,2,*0*)
 
P

Peo Sjoblom

What kind of values are you looking up, if you want exact match you SHOULD
use FALSE or 0 at the end of the formula, if you are looking up numeric
values that you want to return largest value that is less than or equal to
lookup then do not use FALSE, otherwise use it. If you get an error then the
values are not equal, they could be imported with leading/trailing spaces
that will be enough
Compare 2 values that you think are the same

=A1=A6

if that returns false theye are not equal


Regards,

Peo Sjoblom
 
A

Aladin Akyurek

Tina said:
I have a vlookup formula put in place to look at column M (a year), find
the matching year in column A on a different tab and return the value in
column B =VLOOKUP(M6,'ENR Index'!A:B,2)
Very simple formula.

The problem is - after the 6th line on the worksheet, the formula
returns a value of N/A.
I have sorted the sheet a number of different ways, but continue to
receive the same fomula in the 6th line.
I have entered False and True in the the formula but still come up with
nothing.


Can anyone assist with this?
Thank you
Click to expand...

Try to run the TrimAll macro on A:B on ENR Index in order to remove
unwanted chars from the cell(s) of interest.

Moreover, if A:B is sorted in ascending order and you can maintain the
range sorted, keep your current VLOOKUP formula:

=VLOOKUP(M6,'ENR Index'!A:B,2)

If you nevertheless need an exact match, you can invoke the faster:

=IF(VLOOKUP(M6,'ENR Index'!A:A,1)=M6,VLOOKUP(M6,'ENR Index'!A:B,2),"Not
Found")
 
Top