vlookup still gives na

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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!
 
Nikki:
You mention if I still get the NA errot to use the iserror () formula.
What is that? I am still getting NA's.
 
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.
 
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.
 
Dave:
See formula below:
=VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx)
I get #NAME?
 
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...
 
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
 
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...
 
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)
 
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...
 
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
 
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...
 
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...
 
Back
Top