#### VLOOKUP Question 11-14-09

```I feel stupid but I cant figure out what is wrong with my forumla. It will
only return #N/A.

Here is the formula:

=(VLOOKUP(J2,List!\$A\$1:\$G\$404,6,FALSE))

Where "List" is another tab on the same workbook and 6 is the column that I
want ito lookup.
```
 0
Utf
11/14/2009 10:01:01 PM
excel.misc 78881 articles. 5 followers.

3 Replies
1514 Views

Similar Articles

[PageSpeed] 49

```You don't need those outside ()'s, but that won't matter.

=VLOOKUP(J2,List!\$A\$1:\$G\$404,6,FALSE)

My guess is that you don't have an exact match in List!A1:A404 for the value in
J2.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

>
> I feel stupid but I cant figure out what is wrong with my forumla. It will
> only return #N/A.
>
> Here is the formula:
>
> =(VLOOKUP(J2,List!\$A\$1:\$G\$404,6,FALSE))
>
> Where "List" is another tab on the same workbook and 6 is the column that I
> want ito lookup.

--

Dave Peterson
```
 0
Dave
11/14/2009 10:56:44 PM
```Hello,

Sometimes the vlookup fails because what you are looking for is not the same
as what you have given.

It is one of the classic issues in computing when 1 does not equal 1.

You are probably trying to compare a number with a text representation of 1.

One of the cells is probably formatted as text or it has quotes or a single
quote on it.

You can solve this by converting the search to text as in =TEXT(1,"0").

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK

> I feel stupid but I cant figure out what is wrong with my forumla. It will
> only return #N/A.
>
> Here is the formula:
>
> =(VLOOKUP(J2,List!\$A\$1:\$G\$404,6,FALSE))
>
> Where "List" is another tab on the same workbook and 6 is the column that I
> want ito lookup.
```
 0
Utf
11/14/2009 11:15:01 PM
```Nothing wrong with your vlookup formula, other than the extraneous outer
parens. The problem lies in the data that you're trying to match. It looks
like it should match but it doesn't. Could be either text numbers vs real
numbers issue and/or the presence of extra white spaces for text matches
which is throwing things off.

With your data as-is (ie w/o you having to clean/modify the source
data/lookup values), you could try this index/match for more robust results,
normal ENTER to confirm will do:
=IF(LEN(J2)=0,"",INDEX(List!F\$2:F\$404,MATCH(TRIM(J2&""),INDEX(TRIM(List!A\$2:A\$404&""),),0)))
Copy down. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
> I feel stupid but I cant figure out what is wrong with my forumla. It will
> only return #N/A.
>
> Here is the formula:
>
> =(VLOOKUP(J2,List!\$A\$1:\$G\$404,6,FALSE))
>
> Where "List" is another tab on the same workbook and 6 is the column that I
> want ito lookup.
```
 0
Utf
11/14/2009 11:18:01 PM

Similar Artilces:

windows mp 11 for xp
I have downloaded wmp 11 to my Xp but it deos not play my music files while real player plays all those mp3 files. -- sat On Mon, 16 Nov 2009 01:45:01 -0800, satish <satish@discussions.microsoft.com> wrote: >I have downloaded wmp 11 to my Xp but it deos not play my music files while >real player plays all those mp3 files. What steps are you taking to play them ? If real player is set as the default player for MP3 files, then WMP will never open to play them. You can try adding those filers to the media library in WMP : Press F3 key in WMp to show 'Monitor F...

RE: MailQ Utility question...
I have Exchange 2000 SP3 running Windows 2000 SP4 and using Outlook 2003. In my MAILQ utility that I see on the web browser, under the SMTP category and then Remote, that number is at 9034. I believe they are related to spam mail and exchange is trying to talk send mail back to sender. Does anyone have this problem and is there anything I can do to minimize the number? Thank you in advance DM ...

Blackberry question #2
We keep every single message since the dawn of time on our exchange 2003 server. Yes, every message even since MS Mail. Luckily we are only 60 users and the total of all mailboxes is only about 16 GB so our 3.4 ghz, 1GB of RAM, and RAID array server can handle 10 times the load, but I don't know if a Blackberry can handle the President's mailbox He is interested in getting a Blackberry and I was curious as to how his 800MB mailbox would interact. I would assume that the Blackberry's do not have anywhere near that capacity so how do I manage him getting only the messages ...

Vlookup #5
Hello All, Using Excel XP: I have a lookup table that has 3 colums of information eg. A B C 1 4 1 3 2 4 2 2 3 4 3 1 4 4 4 0 ------------------------------ 5 4 3 =VLOOKUP(\$A5,Database,3,0) in VLOOKUP it takes the data in column A to return C but I would like VLOOKUP to take the data in Column A & Column B then return the value in C. How would I set up the formula to do that , if it's possible. Thank you, Michael Clarification? -- Don Guillett SalesAid Softwar...

exporting and archiving question
I have a couple of problem transactions that I cannot edit. Salvage/repair not working, no restorable backup. I do have a backup file from May of this year, I was wondering if there is any way to export the transactions from may thru july? When I tried an export, it exported everything and didn't ask for a date. I suppose I could make a copy of the file and delete everything else, and then export. Other thing I tried was to archive my file, getting rid of the problem records. I specified the date to archive before, but EVERYTHING is still in my archive file. Is this just ...

Text function 12-11-09
i have a database which i need to extract the supplier name and they are not in same order for example RPO-09393-Supplier AAA 980-Supplier BBBBBB AHU-OIJ-JUH-Supplier CC As you can see the suppliers name are always at the back, how to build the formula just to extract the supplier name (different lenght) that always at the back. Thanks Howard =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)) "Howard" wrote: > i have a database which i need to extract the supplier name and they are not > in same order for example > > RPO-...

File transferring question
Dear all, can anybody show me example on how to send file from source to destination (through network) by bytes and save it as a file? thank you Are you wanting to know how to copy (or move) a file from one location to another? "Bryan Gan" <cygan@myopensys.com> wrote in message news:edZDEuDQDHA.2432@TK2MSFTNGP10.phx.gbl... > Dear all, > can anybody show me example on how to send file from source to destination > (through network) by bytes and save it as a file? > thank you > > ...

Shop-Vac 963-14-00 Ultra Blower Wet/Dry Vacuum 14-Gallon, 5.5-HP
Price:\$217.23 Image: http://discountadvisors.info/image.php?id=B0001FFLD0 Best deal: http://discountadvisors.info/index.php?id=B0001FFLD0 Tank Capacity: 14 Gallons Power: 5.5 Peak HP Included Accessories: 2-1/2" Dia. X 8' Lock-On hose, (2) 2-1/2" extension wands, 14" floor nozzle with brush, squeegee, 8" utility nozzle, crevice tool, concentrator nozzle, elbow grip, hose holder, accessory basket. Included Filters: Foam Sleeve, Cartridge Filter, Disposable Filter Bag Features: Cord Length: 18 Feet Operation Sound Level: Super Quiet Hose Size: 2.5" Diameter ...

Control Group Question
I have a table that contains member address and I have a table using recipient addess. These tables are linked via member number. I currently have the database linked to a mail merge in word. I have set up an option button in my form and asks which address I want my letter to be sent. Would I then set up a query to link to my mail merge and if 1 use member information and if 2 use recipient information? I am a novice when it comes to writing code, so any help on the code or any better option would be of great help -- Karen K Im not experienced at mail merges, but it seems like you are....

Count Question
Hello, Quick question. Is there any way to do a count referencing a field like below and outputting the number to another field, for example... FieldX 123, 145, 256 Would equal a count of 3 FieldX 254, 655, 673, 4363 Would equal a count of 4 Please note the commas do exist in the field, and each string will be distinct. Thank you! And Happy Holidays! -- Message posted via http://www.accessmonster.com A field with multiple values in it, separated by commas, is not an optimal relational design. If your situation calls for some variable number of values associated with some other...

Junk Mail Question
OL2007 offers an option to mark a 'Sender's Domain to the Safe Senders List' in the Junk-Mail tools. How does one mark a domain to the baddies list for permanent dispatch to hell? With spammers frequently using fraud prefixes but often the same domain, I would imagine that it is essential to be able to blacklist a domain. Does OL2007 not offer that? You should be able to add a whole domain here in Outlook: Actions menu > Junk E-mail > Junk Email options > Blocked Senders tab "ClueLess" <ganz@schrott.com> wrote in message news:O3QYHcqnIHA.1052@TK2...

CRM SDK Questions
Hello, I'm hoping that someone who is familiar with developing CRM customizations can help me get off on the right track here. I'm familiar with developing windows applications, and I also did PHP web apps for a number of years, but I'm new to ASP. I'm trying to write a simple page for updating the Status Reason on cases that are already closed (so that the users don't have to re-open the case, change the status reason, then re-close the case again), and I thought it might be better to use the SDK rather than modifying the SQL database directly. How do I use the ...

Probably very easy question conerning auto-changing dates
I've got a list of dates in column B. Row 1 is labeled "date", Row 2 starts my date data (May-04). I want a list in column B of the preceding 23 months, so columb B ends with Jun-02. Obviously, I can manually input this information. What I can't figure out is how if I go and change the first date (say, to Jun-04) it will auto-change my list of dates accordingly (so my list would now end in Jul-02). NOTE: I know how to create a handle-fill, but it won't update any changes I make to the dates, nor can I get it to go backwards, chronologically. Thanks, Jacob jfarino@ameris...

Customize Outlook Today #11
On the outlook today page, when I click on Customize Outlook today it will not open the customize page. The following MSKB article provides the fix for this issue. OL2000: You Cannot Customize Outlook Today After You Install Critical Update 813489 for Internet Explorer: http://support.microsoft.com/default.aspx?scid=kb;EN-US;820575 -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Tom" <tombasenji@yahoo.com> wrote in message news:007801c35926\$...

Public Folder Move questions
Hello: I recently moved teh public folders from 5.5 to 2003 as per exdeploy instructions. My questions are: Do public folder sizes change when they move to the 2003 they seem smaller in size but same amount of items. Upon moving the folders, WMI is reporting the exchange stuff on Event log BUT when I click on the URL for further info I get a page from MS saying that info is not found etc. Also, one of the folders IS NOT In Sync it states udner replication ' modified locally' Any help is appreciated. Thanks Anna 1. yes, very likely the sizes reported by 5.5 are wrong. 2. that does...

A question of dynamically moving controls (Drag and Drop)
Hello again folks, I am having a little issue with this and would appreciate any thoughts. I have an application where I have a number of button controls that the User can drag and drop around the screen. This works fine. The dropped positions are saved in the registry and when the User opens the application again later, the controls are where ever they moved them to. And this al;so appears to work fine. However, I noticed that when I run on a smaller physical screen, and the display scrolls I have an issue. This is difficult for me to explain, but, say the actual display is twic...

Query search question
I download a list of information onto a table, and i'd like to make a search on that item. I put in "*" & [Search Item] & "*" into the item field with the wildcards just in case. This works fine. Now lets say I'm looking up cars, so in the search input box that pops run when I run the query I type in dodge and that brings backs dodge matches. If I want to look up red dodges do I put like "red" and "*" & [Search Item] & "*" or "red" &"*" & [Search Item] & "*" ? I've trie...

yet another owner-drawn CListBox question (problems with custom background patterns)
Does anyone have any experience of trying to render a custom background under the contents of an owner-drawn CListBox? I'm experimenting with a gradient fill but it could just as easily be a bitmap and what i've noticed is that when the list-box is empty it seems like some kind of internal list-box code is executed to erase the list-box scroll area rather than going through OnEraseBkgnd (which is where i'm drawing the gradient). The result is that everything looks fine providing there are some items in the list but the instant that the last item is deleted the whole scroll ...

Counting question #2
I have a range of cells that I want to 'count' if the number is greater than 0 but less than 6. The cell # is F33 where I want the answer. The range is: Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29. What formula would I use? I've tried several but I keep getting error answers. hi, =count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29) >-----Original Message----- >I have a range of cells that I want to 'count' if the number is greater than >0 but less than 6. The cell # is F33 where I want the answer. The range is: >Q13:W13; Q17:W17; Q21:W17; Q25:W25...

PST synching question?
Currently I put my pst file (desktop) on a usb disk and then load it to my laptop and work on it. Later I reverse the process, like at the end of the day when I want to put it back on the desktop with updates. Question: Is this dangerous/risky or would it be better to use a synching program to sychronize pst changes from laptop to/from desktop? I've had no problems yet but I am wondering am I taking any risk, and since I have seen a lot of synching programs (indicating that pst download/upload is not too popular) maybe I am doing it wrong. Thanks Kita <kita@kit.com> wrote in...

Group By
I have a view which uses group by - containing 2 aggragate functions, Sum() and Count(). What I want to do is include in my Select portion of my view , a column that I do not want to be included in the Group by. Is there a simple way to do this? Example: Select A.Field1,B.Field2,A.Field3,A.Field4,A.Field5,Sum(Field2),Count(*) From tablename A Left Outer Join Tablename2 B on B.Rec_ID = A.Rec_ID Group by A.Field1, B.Field2 A.Field3 A.Field4 (Note: In this case A.Field5 is EXCLUDED in the Group by) Your help is greatly appreciated. -Ray Field2 is in the ...

Question about how to use range in VBA
Hello, I am trying to cut and paste one range from one sheet to the same range in a different sheet. I used the following code: Range(Cells(startrow, 1), Cells(endrow, 12)).Select Selection.Copy Worksheets("SUMM").Activate Range(Cells(startrow, 1), Cells(endrow, 12)).Select --- ERROR HERE ActiveSheet.Paste This code gives me an error where indicated: Application defined or Object defined error Notice that I use the exact same line of code three lines earlier and I get no error. If I change my code to read as Range(Cells(startrow, 1), Cells(endrow, 12)).Sel...

SUMPRODUCT with date range question
Hi all, I'm having trouble creating a formula as follows: Column A: either Y or N Coumn B: a forecasted date Column C: \$\$ amount of transaction What I would like to do is create a report that shows the sum of column C for anything where column A is "Y" and the date in column B is today's date or up to 90 days after today's date (Today +90)... I'm having problems with the date criteria, and would appreciate your help! Thanks try something like this =SUMPRODUCT((A7:A10="Y")*(B7:B10>=TODAY())*(B7:B10<=TODAY()+90)*C7:C10)- Don Guillett SalesAid So...

Going From Excel 97 To Excel 2007 Questions From Sr. Citizen, Please
Hello, Am in my 70's now, and a lot of this is becoming a bit harder. May I ask, please: a. Have been using Excel 97 to simply plot out my blood pressure for my MD over the years. Old Excel, but did everything I needed, and was sure easy to make plots from. Just installed Office 97 with the new Excel. Big mistake, I think. My old graphs in Excel 97 (it's been so long now I don't remember all the things I did to create it) has a "trend line" thru the points. Guess it's a simple linear rms line fit. The new Excel, when importing the old file, has the points and...