vlookup still gives na

M

mayoslc

We are trying to manipulate numbers using vlookup, approximately 4,300
records using the formula below:
=VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE)
There is one total property spreadsheet and 3 other tabs. About 1,500
records are coming back NA.
Any suggestions on what might be happening?? It looks like a formula problem!
 
N

Nikki

you need to USe $ signs..
=VLOOKUP(B2,'Tier 1'!$A:$A:'Tier 1'!$E:$E,5,FALSE)

if you are still getting NA is because there are missing data in where you
are doing the lookup. you can get rid of that by using and iserror() formula.

Nikki
 
B

Bob Phillips

Are you sure that the value in B2 is in column A of Tier 1? That is what
#N/A usually means, or that column E contains a #N/A.

--

HTH

RP
(remove nothere from the email address if mailing direct)


mayoslc said:
We are trying to manipulate numbers using vlookup, approximately 4,300
records using the formula below:
=VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE)
There is one total property spreadsheet and 3 other tabs. About 1,500
records are coming back NA.
Any suggestions on what might be happening?? It looks like a formula
Click to expand...
problem!
 
M

mayoslc

Nikki:
You mention if I still get the NA errot to use the iserror () formula.
What is that? I am still getting NA's.
 
M

mayoslc

Bob:
I am trying to compare column B in worksheet called "Data" to the data
on worksheet called "Tier 1" using column E and column F.
Part of the information is being pulled over but I have about 25% that
comes up NA.
 
M

mayoslc

Dave:
Tried this formula below and it did not work.
=VLOOKUP(B2,'Tier 1'!A:E,5,FALSE)
 
D

Dave Peterson

What's in B2?

In fact, find that cell in Tier 1 column A that looks like it should be a match.

Put this in an empty cell of the sheet with the formula.

=b2='tier 1'!Axx

If that returns false, then there is a difference between what's in B2 and what
is in that cell that you think should match.

Common troubleshooting techniques can be found at Debra's site.
 
M

mayoslc

Dave:
See formula below:
=VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx)
I get #NAME?
 
D

Dave Peterson

First, this is the formula that I wanted in a separate cell:

=b2='tier 1'!Axx

And change xx to match the row where you think B2 matched.

And I'd still recommend using this as your =vlookup() formula:

=VLOOKUP(B2,'Tier 1'!A:E,5,FALSE)

=====
And more separate questions:

What's in B2?

And did you look at Debra's site?
Dave:
See formula below:
=VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx)
I get #NAME?
Click to expand...
 
M

mayoslc

Dave:
More information. B2 has property name text on a worksheet called Dwaynes
List. Worksheet 2 is called Tier 1. In column A on this worksheet I have the
same property name. I am trying to compare property names and pull in orders
listed in column E of the same worksheet. I use your formula =b2='tier 1'!A2
to compare property names and I get a FALSE reply.
Hope you can help me out!!!!!

Kraig
 
D

Dave Peterson

Does A2 on "Tier 1" look like it should match B2?

If you think it should match and you get False, then there is a difference
between what's in those cells.

Again, did you look at Debra's site?

What's the value in B2--what's the actual property name?

If it's text, then look for differences--spelling, extra spaces.

In fact, copy B2 and paste it in your reply on a separate line.

Then paste what you think should be the matching cell in column A of the Tier 1
sheet.


Dave:
More information. B2 has property name text on a worksheet called Dwaynes
List. Worksheet 2 is called Tier 1. In column A on this worksheet I have the
same property name. I am trying to compare property names and pull in orders
listed in column E of the same worksheet. I use your formula =b2='tier 1'!A2
to compare property names and I get a FALSE reply.
Hope you can help me out!!!!!

Kraig
Click to expand...
 
M

mayoslc

Dave:
I did look at Debra's site last night and everything I tried did not work.
Sample of B column below including title. B2 is listed as 83rd ave.
PROPERTY NAME
83RD AVENUE AND ENCANTO
ACACIA HILLS
ACACIA TRAIL
ADERRA CONDOS
ADOBE RIDGE APARTMENTS
ADVANTAGE POINT
AGUA FRIA RANCH PHASE 1
AGUA FRIA RANCH PHASE 2
AGUA FRIA RANCH PHASE 3
AGUA FRIA RANCH PHASE 4
ALEXAN AT FRANK LLOYD WRIGHT
ALEXAN AT PALM VALLEY
ALEXAN BELLEVIEW
ALEXAN PECOS SPRINGS
ALIANTE
ALTAMIRA APTS
AMBER RIDGE APTS
AMETHYST ARBOR
AMETHYST GARDENS
ANASAZI VILLAGE
ANTELOPE VALLEY APT'S

Sample of Tier 1 worksheet, column A
ACAPELLA
ADAGIO (AZ)
ADOBE RIDGE APARTMENTS
ALEXAN AT FRANK LLOYD WRIGHT
ALEXAN BELLEVIEW
ALEXAN PECOS SPRINGS
ALLEGRO APTS
AMETHYST GARDENS
ANDANTE APTS
ANDOVER PARK
ARBOR VILLAGE (AZ)
ARBORETUM
ARCHSTONE DEER VALLEY VILLAGE I, II
ARIOSO CITY LOFTS
ARROWHEAD LANDINGS
ARROWHEAD SUMMIT
AUGUSTA RANCH
AUTUMN CREEK (AZ)
 
D

Dave Peterson

Is there a cell in column A of 'Tier 1' that has this in it:

83RD AVENUE AND ENCANTO

It has to match exactly--all the characters--all the spaces.


Dave:
I did look at Debra's site last night and everything I tried did not work.
Sample of B column below including title. B2 is listed as 83rd ave.
PROPERTY NAME
83RD AVENUE AND ENCANTO
ACACIA HILLS
ACACIA TRAIL
ADERRA CONDOS
ADOBE RIDGE APARTMENTS
ADVANTAGE POINT
AGUA FRIA RANCH PHASE 1
AGUA FRIA RANCH PHASE 2
AGUA FRIA RANCH PHASE 3
AGUA FRIA RANCH PHASE 4
ALEXAN AT FRANK LLOYD WRIGHT
ALEXAN AT PALM VALLEY
ALEXAN BELLEVIEW
ALEXAN PECOS SPRINGS
ALIANTE
ALTAMIRA APTS
AMBER RIDGE APTS
AMETHYST ARBOR
AMETHYST GARDENS
ANASAZI VILLAGE
ANTELOPE VALLEY APT'S

Sample of Tier 1 worksheet, column A
ACAPELLA
ADAGIO (AZ)
ADOBE RIDGE APARTMENTS
ALEXAN AT FRANK LLOYD WRIGHT
ALEXAN BELLEVIEW
ALEXAN PECOS SPRINGS
ALLEGRO APTS
AMETHYST GARDENS
ANDANTE APTS
ANDOVER PARK
ARBOR VILLAGE (AZ)
ARBORETUM
ARCHSTONE DEER VALLEY VILLAGE I, II
ARIOSO CITY LOFTS
ARROWHEAD LANDINGS
ARROWHEAD SUMMIT
AUGUSTA RANCH
AUTUMN CREEK (AZ)
Click to expand...
 
M

mayoslc

Dave:
No, is there a work around? Tier 1 worksheet, and Tier 2, 3 worksheet
does not have 83RD AVENUE AND ENCANTO!
Please advise as soon as you can.

Kraig
 
D

Dave Peterson

If it's not there, then you won't find a match.

You can stop the errors from showing up:

=if(iserror(VLOOKUP(B2,'Tier 1'!A:E,5,FALSE)),"",
VLOOKUP(B2,'Tier 1'!A:E,5,FALSE))

(all one cell)

but you can't find a match if there is no match.
Dave:
No, is there a work around? Tier 1 worksheet, and Tier 2, 3 worksheet
does not have 83RD AVENUE AND ENCANTO!
Please advise as soon as you can.

Kraig
Click to expand...
 
D

Dave Peterson

Glad it worked for you.

I was under the misunderstanding that you actually had matches for those
entries--not just that you wanted to hide the error value.


Dave:
Much appreciated. Your new formula worked "to a Tea"!!!!

Kraig
Click to expand...
 
Top