| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup still gives na
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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 "mayoslc" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
Nikki:
You mention if I still get the NA errot to use the iserror () formula. What is that? I am still getting NA's. "Nikki" wrote: 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 "mayoslc" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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. "Bob Phillips" wrote: 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" wrote in message ... 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
I would think this syntax would be more common:
=VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
Dave:
Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
Dave:
See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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. mayoslc wrote: 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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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 "Dave Peterson" wrote: 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. mayoslc wrote: 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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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. mayoslc wrote: 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 "Dave Peterson" wrote: 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. mayoslc wrote: 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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
Dave:
Much appreciated. Your new formula worked "to a Tea"!!!! Kraig "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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. mayoslc wrote: 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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup still gives na
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. mayoslc wrote: Dave: Much appreciated. Your new formula worked "to a Tea"!!!! Kraig "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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. mayoslc wrote: 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) "Dave Peterson" wrote: 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. mayoslc wrote: 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 "Dave Peterson" wrote: 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? mayoslc wrote: Dave: See formula below: =VLOOKUP(B2,'Tier 1'!A:A:'Tier 1'!E:E,5,FALSE=B2='Tier 1'!Axx) I get #NAME? "Dave Peterson" wrote: 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. mayoslc wrote: Dave: Tried this formula below and it did not work. =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) "Dave Peterson" wrote: I would think this syntax would be more common: =VLOOKUP(B2,'Tier 1'!A:E,5,FALSE) And Debra Dalgleish has some troubleshooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble mayoslc wrote: 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! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
