isna vlookup

Hi

I'm using this vlookup to get values from a sheet 
named "sub": VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE)
 and t works. But when "R1" is not in the lookup table 
sub!$J$1:$K$18 I get an #N/A as a result and I changed 
the vlookup formula to:
 =IF(ISNA(VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE),"",VLOOKUP
(R1,sub!$J$1:$K$18,2,FALSE)) to avoid #N/A but now I 
receive an error message for the formula. 
Is there anybody who knows how to fix this problem? 

0
anonymous (74722)
4/3/2005 7:45:25 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
389 Views

Similar Articles

[PageSpeed] 31

Tim wrote:
> Hi
> 
> I'm using this vlookup to get values from a sheet 
> named "sub": VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE)
>  and t works. But when "R1" is not in the lookup table 
> sub!$J$1:$K$18 I get an #N/A as a result and I changed 
> the vlookup formula to:
>  =IF(ISNA(VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE),"",VLOOKUP
> (R1,sub!$J$1:$K$18,2,FALSE)) to avoid #N/A but now I 
> receive an error message for the formula. 
> Is there anybody who knows how to fix this problem? 
> 

A paren is missing:

=IF(ISNA(VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE)),"",VLOOKUP
(R1,sub!$J$1:$K$18,2,FALSE))
0
akyurek (248)
4/3/2005 8:27:25 AM
Thank you Aladin.

0
anonymous (74722)
4/3/2005 10:23:05 AM
Reply:

Similar Artilces:

Empty cell in vlookup
hi, is there a way to have a vlookup see an empty cell and rather than filling it in with a 0 it could leave it blank? here's my vlookup formula =IF(A21="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)) Thank you in advance for your help :) =IF(A21="","",if(VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE))) -- Please click "yes" if this post helped you! Greatly appreciated Eva "confused" wrote: ...

Vlookup and min formula
I have a worksheet that has dates in column B and file # in column A. In cell J12, i have this formula =min(B2:B100), in L12, I have a vlookup to return the information in column A that matches the date =vlookup(J12,A2:B100,1,0). I receive a #name error. Any ideas? VLOOKUP goes left to right. Are you able to move dates to A, and file names to B? what about having filenames in both A and another column to the right of the dates column (say in column C enter the formula =A12 in cell C12... then change the range in your VLOOKUP to B:C)? since your lookup value is a date returned wit...

vlookup returns na
Hi all! Sheet1 contains the following: Col_H Col_O 302 33937 158 32840 3582 73345 39760 73455 4412 AGL92 2941 AHW92 136 30044 Sheet2 contains this: Col_H 34132 34332 33532 31032 34433 34633 33937 This is the function I use along Col_I: =IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O $7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O $7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE)) For H7 I would expect the function to return 302, but it only retuns #N/A for all rows in Col_H. I use concatenate to generate the data in Col_O in Sheet1. I also tried to copy the valu...

VLOOKUP Problem in EXCEL 2010
I have a reference table (named SPOTS) that looks like this Date GoldSpot Silver Spot 2/9/2012 $1,722.00 $33.59 2/21/2012 $1,753.00 $34.15 2/22/2012 $1,754.90 $34.19 2/23/2012 $1,780.00 $35.35 2/24/2012 $1,781.00 $35.41 12/31/2099 In a second table, I want to use VLOOKUP to find a match in Column A and return either Column B or Column C on the row that contains the match. The second table (VALUES) looks like this (much simplified) after update: Date GoldSpot Quantity Value 2/9/2012 $1,722.00 3 $5166.00 2/21/2012 $1,753.00 5 $8765.00 2/22/2012 $1,754.90 1 ...

Help Please with Dropdown lists and Vlookup
How can I integrate using a multiselect dropdown list and vlookup function? I have gone to "Contextures" website but do not see this type of example. I am trying to setup a form with a couple of list boxes where if I select the first box and select an item(s) they will be filled in the adjoing cell and the other list boxes will be populated with the data pertaining to the item selected in the first list box. Any help or reference to any site will be greatly appreciated. Hi Totally Confused, You say that you have looked at the Contextures site, but did you look at Debra Da...

Matching and VLookups
I have just used some vlookups to find some information on a Unique ID. Unfortunately each week a few of these ID's are deleted and some are added- so a vlookup can not find information pertaining to them. The good news is that the data is sorted in such a way that when a vlookup returns a NA, b/c it can't find info on the related to the ID, that all data above it is what I need. For instance, after running a Vlookup I left with: ID's Region location 1 NY NYC 2 NA NA 3 CAL LA 4 ...

Vlookup ref!
We are doing a vlookup for a date cell in both workbook which is in column D this is the formula =VLOOKUP(B10,'C:\Documents and Settings\Administrator\My Documents\[calling_.XLS]Customers'!$A$4:$I$58,4,FALSE) but we are getting a #REF! both are date fields also it is in row 30 column d? Did you type that reference in? If so then open up both workbooks, tile them so you can see them both and then use the mouse to select that range on the calling_.xls workbook from within the formula. Close the target workbook and it will automatically create the correct range reference for you...

Vlookup question #5
I have to search and put values in one of my sheets after looking in a field of a field. I believe i need to to a nested vlookup, but im not sure how to go about it. I copied the relevant portions of the data below: Department PriceType Monthly Average NY NY Average $1,000.00 NJ NJ NJ NJ Average $1,500.35 I have to search within a department (for example NJ), and then search where average is occuring in the Price Type field, and then pick the corresponding Monthly a...

Vlookup with Images
I'm currently using vlookup to select paragraphs of information depending on a dropdown list. The vlookup refers to an array in another sheet. Is there any way of showing an image as well so that when I pick from a dropdown list, it selects an image from one of the cells in the lookup array? Thanks Mike Mike Yes, there is a way. You can employ event code and VLOOKUP with Data Validation list selection to choose which picture to show? See JE McGimpsey's site for more on lookuppics and a sample workbook. http://www.mcgimpsey.com/excel/lookuppics.html Gord Dibben MS Excel ...

Vlookup #11
I am trying to use a vlookup to find a id number from a range of cells and if found have it insert the data form the column 2. It seems to be working but it is not pulling the exact number. here is my formula. =VLOOKUP(E6,$BM$2:$BN$22,2) Column E6 contains all the Id numbers The array also has the id's with a Grade in column 2 How can I make it pull the exact number that is looking up. Thanks for your Time If not sorted by number use ,0 or ,false at the end. Look in the help index for VLOOKUP =VLOOKUP(E6,$BM$2:$BN$22,2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software d...

Excel 2007: Vlookup Problem
I don't use Vlookup much and it's not working for me. I know it's user error so can you tell me what I did wrong? I have two worksheets in my file. In the first one, I have a list of addresses with a full unabbreviated US state name in column T, row 2. Column U is blank because I want to put the two-digit state code in there (starting with U2). In the second worksheet I have the full state name in Column A and the two-digit code for it in Column B (rows 1-50). Here's my formula starting in U2: =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) What is wrong? ...

Updating Excelsheet with conected VLOOKUP sheets time consuming
I have a workbook with several sheets. Some sheets connects via VLOOKU to an updatable sheet with raw data. When I try to update data (impor from database) to the raw data sheet. It takes forever and I think thi is casued by the "linked" VLOOKUPs to from the other sheets to thi rawdata sheet, beacuse when I have tried to update the rawdata shee without "connected" VLOOKUP sheets it does not take forever. Is it possibel to solve this? I have tryed Application.ScreenUpdating = False without success, maybe because the VLOOKUPs are in the othersheets no in the rawsheets th...

error (bug?) with VLOOKUP function
Hallo, I have a problem with VLOOKUP function (excel 2007). I tried what follows several times and i always get the same error/ considere table (B5:C9) sorted according the first column: name salary baba 400 bibi 600 bobo 200 bubu 300 cel B13 contains: bibi cel B14 contains: baba in D13: =VLOOKUP(B13;B$5:C$9;2) and i drag it to D14 Result: in D13: 600 (this is correct) in D14: #N/B Each time, with the first name only of any sorted table, i get that error. Any explanation? Thanks Luc see answer in other newsgroup -- Kind regards, ...

VLOOKUP for ZIP codes??
Hi all. I work for a college and we've divided up the country, by zip code, into many many many different regions. What I have is a worksheet in excel that looks like this 'MinZip' 'MaxZip' Region 75000 75899 DALLAS 76000 76899 DALLAS Where MINZIP s the beginning number of a ZIPCode range for a particular region, and MAX is the terminating zip code. On a separate form, I have the listing of our applicants and their zip codes. I was to create a lookup formula that says (in english): If REALZIP is between MINZIP and MAXZIP on row one, re...

help on a vlookup
I have on sheet 1 a check register, (Date/Ck. No./Disc./Amount), and, would like to make some kind of vlookup. that if the description i (let say) MCI, should appear on sheet 2 the whole line from sheet 1 and if MCI appears on line 3, and line 9, it should appear on sheet 2 one under the other and so on -- Message posted from http://www.ExcelForum.com An easy way is just to sort and copy or use data>filter>autofilter and copy or use a macro to do it for you. -- Don Guillett SalesAid Software donaldb@281.com "shlomop >" <<shlomop.1bx7bw@excelforum-nospam.com> w...

vlookup to another worksheet
I have a filled column in worksheet_1 called "Reference". I have another empty column in that worksheet, called "Data" that I want to populate with values from worksheet_2. Worksheet_2 has the necessary reference number to link to My question is "how can I do a lookup on "Reference" in worksheet 1, pick up the same reference number in worksheet 2 and then do the vlookup on that to bring the correct data value over. Thanks Rob Something like this: =3DVLOOKUP(Reference,worksheet_2!A:B,2,0) then copy down. I've assumed that the...

vlookups sheet is too big
Group, I created a sheet a while back with Vlookups all over the sheet which is currently 14 Megs im trying to get the sheet down to a reasonable size. Does anybody know of a way to do this. Thanks in advance, Fish Fish Perhaps Excel estimates your actual range of data as far larger than it really is. To reset the "used range" http://www.contextures.on.ca/xlfaqApp.html#Unused Gord Dibben Excel MVP On Mon, 8 Nov 2004 09:34:17 -0800, "Fish" <anonymous@discussions.microsoft.com> wrote: >Group, > >I created a sheet a while back with Vlookups all over...

VLookup Variable Offset
I want to use a macro to set an active cell to return a value from the 2nd column of an array called RollLastRecost. If specific row is empty I want no value to be the result. I have a formula that works if I manually type it in, and copy it down through the other rows but I want the macro to put the formula in for each row using ActiveCell.FormulaR1C1. I'm not sure how to make the B3 cell reference change as the macro moves through the other rows. This is what I use manually If(ISNA(VLookup(B3, RollLastRecost,2,False))," ",VLookUp_(B3,RollLastRecost,2,False)...

How to use VLookup with a range of numbers?
Hi I'm trying to solve this problem I have and could use some help, I was wondering if its possible to use VLookup to check through a range of data in one cell, ex: 89-100 or 71-74. But I'm not sure if there is a way to make excel understand FROM 89 TO 100 0r 71 TO 74. (Yes I've just started learning excel on my own) This is the problem I'm trying to solve: To calculate the data in the 20 Point Scale column, enter a formula that converts each student’s Final Percentage to a number grade on the 20 Point Scale as outlined below:- 80% and above = 1 75-79% ...

vlookup formula question #2
For the formula below, =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"no","yes") What is "ISNA" and what does each value separated with comma represent? Thanks in advance! -- Message posted via http://www.officekb.com Hi Janice not meaning to be unhelpful here, but you should check Help out for a good explaination of both of these questions. Depending on your version - ISNA is probably listed under a page called IS FUNCTIONS and VLOOKUP should come up if you type it into the paperclip (office assistant) or the ask a question box. Alternatively using the Pas...

vlookups and hyperlinks
Hi, I am a conservation biologist working in Australia and unfortunatel I do not have a comprehensive knowledge of excel. I am in the proces of creating a series of workbooks each of which contain lists of large number of vegetation types which occur in particular geographi regions. Currently I am manually inserting hyperlinks from each typ to a detailed description of that type (which are all held on separate worksheet in the workbook). I have been attempting t automate this somewhat by using vlookup to reference to a list o hyperlinks. However rather than returning a hyperlink referen...

VLOOKUP Headache
Hi all, I have a major problem with using VLOOKUP, here is a simple example of what i want to achieve... I have a spreadsheet with 5 worksheets; MAIN ARSENAL ASTON VILLA BIRMINGHAM BLACKBURN on the 'MAIN' worksheet the columns are A DATE B TEAM1 C TEAM2 D TEAM1 AVG E TEAM2 AVG There are hundreds of lines of data for 'DATE','TEAM1'&''TEAM2' I want to do a VLOOKUP in column D 'TEAM1 AVG' that looks at the date in 'DATE', looks at the team in 'TEAM1' and goes to the relevant worksheet for that team then looks for that date and...

Vlookup hyperlink
Dear All I am using Office 97 :( And want to try to do a vlookup but the value I want to retrieve is a hyperlink, when doing this it returns the text but the hyperlink doesn't work Is there anyway this can be done Thank Adam Hi Adam try something like =HYPERLINK(VLOOKUP(....)) -- Regards Frank Kabel Frankfurt, Germany Adam wrote: > Dear All, > > I am using Office 97 :( And want to try to do a vlookup but the value > I want to retrieve is a hyperlink, when doing this it returns the > text but the hyperlink doesn't work. > > Is there anyway this can be done? ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Sorting help-vlookup?
Worksheet2. Column A is the name of a project Column B is a date Column C is word count Column D is photo count There are about 400 rows of information under those headings. My problem is that Column A is messed up. The same project may be named two or three different ways. I need to find the name of a project by keyword (vlookup?) and by the latest date. Then I need that row to appear on a new Worksheet3 under official names that I define. If this is a vlookup, I'd really appreciate a formula that allows me to look up the keyword "Clack" and put that row under my row called...