=IF(NOT(ISNA(VLOOKUP Function

I am using Excel 2003

I am trying to produce a summary shett from five differnet worksheets.

I am using the following function 
=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"").

It works fine until I reach 18 and above and it returns #REF!.

Does this formula not work after 17 or is there another formula that I 
should be using.

Colin
0
Utf
12/1/2009 9:44:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1019 Views

Similar Articles

[PageSpeed] 25

Hi,

The VLOOKUP() table array cannot span across sheets.  Do let us know what 
you are trying to do?

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CWH" <CWH@discussions.microsoft.com> wrote in message 
news:A8D1BC10-0B7F-4AD3-BC52-C93F9F518DA4@microsoft.com...
> I am using Excel 2003
>
> I am trying to produce a summary shett from five differnet worksheets.
>
> I am using the following function
> =IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"").
>
> It works fine until I reach 18 and above and it returns #REF!.
>
> Does this formula not work after 17 or is there another formula that I
> should be using.
>
> Colin 

0
Ashish
12/1/2009 9:49:08 AM
It should not work for 17 either because the range D to S is only 16 columns. 
Your formula should also look like this.

=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:S147,16,FALSE))),VLOOKUP(E7,Tests!D1:S147,16,FALSE),"").


"CWH" wrote:

> I am using Excel 2003
> 
> I am trying to produce a summary shett from five differnet worksheets.
> 
> I am using the following function 
> =IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"").
> 
> It works fine until I reach 18 and above and it returns #REF!.
> 
> Does this formula not work after 17 or is there another formula that I 
> should be using.
> 
> Colin
0
Utf
12/1/2009 10:03:12 AM
Reply:

Similar Artilces:

Search Function with queries
Hi guys, new user to access 2000 and i am trying to create a search query that will allow me to enter in a value and return that value. The part i am having trouble with is i need to do this for many fields and if i use the method i came up with, when i leave a entry empty using the "AND" function i get nothing and when i use the "OR" function i get all the values for both. maybe you guys can help me get in the right direction. this is the command i am using in SQL: SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod Machine list]...

Customer Statement - Footer summary
-------------------------------------------------------------------------- 7/1/2005 11:34:00 AM CDT -- Stephanie Drilling Can you asssit with a report modification issue? (8248456) We are working on the User Defined Statement format. We have national accounts Cash is applied at the parent account level We wanted a statement that provides a detail of the items by child account with subtotal, then in the report footer, a summary of child account with balance. We can get everything but the footer to do what we want. I have enclosed screen prints and the package file of the report. Any i...

Nz function in query
Hi All, the query below shows an error if any of the date in / date out entries in the table are empty. How can i impiment the Nz function to stop this. SQL from query... SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format( [Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...

in access databse the function date() returns #name
i have created an access data base at access 2003 profisonal and used the function date() it worked properly . but when i have coped my data base at another computer at the same office , the funtion retuned (name#) aW4gQWNjZXNzOyB0aGUgRGF0ZSBmdW5jdGlvbiBkb2Vzbid0IHdvcmsgc29tZXRpbWVzLgoKaXQgd29ya3MgYWxsIHRoZSB0aW1lIGluIFNRTCBTZXJ2ZXI7IGJ1dCB5b3UndmUgZ290IHRvIGNhbGwgaXQKR0VUREFURSgpCgoKCk9uIE1hciA3LCAxMToyNMKgcG0sINmF2K3ZhdivINiq2KfYrCDYp9mE2KPYtdmB2YrYp9ihINin2YTYqNiu2YrYqiDZhdit2YXYryDYp9mE2LPZitivCjxAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGU6Cj4gaSBoYXZlIGNyZWF0ZWQgYW4gYWNjZXNzIGRhdGEgYmFzZ...

if function only lets me do 8 ifs
ok here is my scenario.... i want to make a drop down menu for all of my material on an estimate sheet... if i click on one of the materials on the menu i want the 4 columns of info that are related to that product to appear where my drop down is .. ie... material, price, price with tax, price per square foot... some other people tried to help me witht his earlier but one way didnt really apply to me and the other was too advanced for me to understand so i tried this using the if function and i got something like this.. first i made a table on the same sheet with all the 4 columns lets say...

VB Formula / Vlookup
Hi I'm trying to create a macro that populates a cell in a worksheet with a vlookup formula, however, the table array is another workbook and is variable. here is my code so far... Let X = Range("factsaccount") & "." & Range("workstation") Range("NAV") = "=VLOOKUP(""Net Assets"",X$C:$D,2,FALSE)" Thanks -Dennis Maybe this technique will help: dim myRng as range set myrng _ = workbooks("otherworkbook").worksheets("otherworksheet").range("c:d") range("nav").form...

vlookup on cell below
Is it possible to do a VLOOKUP but instead of returning the value in row that contains the lookup, it returns the value in the row below? eg a b 1 no x 2 yes y 3 ok z =VLOOKUP("yes",A1:B3,2,FALSE) would return "z" instead of "y". Th only way I can think of is to add a row header at the top and use the MATCH function in column A to find the row position of "yes", then use that in an HLOOKUP in column B. I was hoping there was a simpler way. Not VLOOKUP, but INDEX & MATCH =INDEX(B1:B3,MATCH("yes",A1:A3,0)) -...

Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...

Public Function to display in text box
Have public function (loaded in the global module) that produces a variable as a double. That all works fine as evidenced by the debug.print method in the immediate window. I desire to have the current value of the variable displayed in a text box. I created a callback function (loaded in global module) that looks like: Public Function GetCurrentWork_GPCI() As Double Dim CurrentWork_GPCI As Double GetCurrentWork_GPCI = CurrentWork_GPCI End Function I can't figure out how to make it display; I've currently got the control source property ...

control a sub/function via a dropdown menu
Hello, I have a dropdown menu on a chart. What must I do to control a sub/function depending on the selection of the dropdown menu. To make it clear .. every time I make a selection on the dropdown menu a function should be executed. Hope somebody can help me! Thanks in advance! daMike Mike - The dropdown is a Forms Toolbar dropdown, so you can right click on it to assign a macro. Also, you can link it to a cell, and the cell holds the index of the selected item. So your macro can check the index, then perform the appropriate actions. - Jon ------- Jon Peltier, Microsoft Excel MVP h...

vlookup-Closest value
Dear Friends, How can I use Vlookup to give me the closest value greather than or equal to vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 It chooses 5.1 and give me the corresponding value. Thank you, atatari wrote: > Dear Friends, > > How can I use Vlookup to give me the closest value greather than or equal to > vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 > It chooses 5.1 and give me the corresponding value. > > Thank you, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) The default behavi...

Separators in Cross Functional Diagrams
I created a swim diagram and added a fill. How do I keep the separators in front? I sent the band to the back, brought the separators forward, and it looks great until I close the document and reopen. Then, the separators don't show up. I changed the translucency of the fill to be able to see them, but I'd like to have them in front. ...

Excel VBA functions
I have learned how to make my own Yield function by writing the code in the VBE window. However, it is saved in my personal macro workbook and everytime I want to use it, I have to type out PERSONAL.MYield.MYield ([arguments]). Is there a way to make it so that I can just type MYield([arguments])? Bonus - Is there another, more user friendly way to make your own functions in Excel? Hi Mokey Check your personal.xls in the VBA editor Tools>References You don't have to type the filename then -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Mokey...

Functions in charts
Is is possible to use functions within charts (for example, if the chart title changes based on the value in a cell)? That way, if the chart data changes, the title also changes to reflect that. Any ideas? On Mar 15, 5:17 pm, "Matt" <MLThorn...@gmail.com> wrote: > Is is possible to use functions within charts (for example, if the > chart title changes based on the value in a cell)? That way, if the > chart data changes, the title also changes to reflect that. > Any ideas? Yes it is. Full instructions are at <a href="http:// office.microsoft.com/en-gb/e...

array function
I'm having trouble making an array. When do you press ctrl-alt-enter -- cutsygur ----------------------------------------------------------------------- cutsygurl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 View this thread: http://www.excelforum.com/showthread.php?threadid=38390 when you edit the formula, you normally exit edit mode by selecting another cell or hitting Enter. At this point, you should hit Ctrl+SHIFT+enter instead. (SHIFT, not ALT) -- Regards, Tom Ogilvy "cutsygurl" <cutsygurl.1rhxmf_1120230340.4504@excelforum-...

What is the function of the MSCREATE.DIR
What is the function of the MSCREATE.DIRs in Office Pro 97? Jack It creates directories during the installation process. "Jack B" <jslimp01nospam@earthlink.net> wrote in message news:%23XbySLS8KHA.3516@TK2MSFTNGP05.phx.gbl... > What is the function of the MSCREATE.DIRs in Office Pro 97? > > > Jack > > You need to read about it yourself here: <http://support.microsoft.com/kb/132978> If you still have any problems then please do not hesitate to come back! hth Jack B wrote: > > What is the function of ...

Re: Other functions don't work now. Help
Arvi; I tried what you have below and now I am getting a #VALUE in colums A & B. Steve > "Arvi Laanemets" <garbage@hot.ee> wrote in message > news:uBPRixxsDHA.2444@TK2MSFTNGP12.phx.gbl... > > Hi > > > > > > > =IF(OR(G1="",ISERROR(SEARCH("risking",$G1))),"",VALUE(MID(G1,SEARCH("risking > > ",$G1)+8,SEARCH("to win",$G1)-SEARCH("risking",$G1)-8))) > > =VALUE(LEFT(K1,FIND(" ",K1))) > > =IF(G1="","",VALUE(MID(G1,SEARCH("to win&qu...

excel function to get tab name
On the first tab in my worksheet, I have several cells in a colum in which the cell reference returns the value of cell A1 from the subsequent tabs. For example, the workbook has 10 tabs named Tab1, Tab2...Tab10, and in Tab1, the formula in cell A2 is "=Tab2!A1", and the formula in cell A3 is "=Tab3!A1"....cell A10 is "Tab10!A1". Sometimes, I will add and delete these subsequent tabs (Tab2 through Tab10) and then have to manually re-link the references to A1 on each of these tabs back to the column on Tab1. Is there some way to do this with a relative r...

Function for add different values
I need one function that adds values that are not equal. Like this: 3 3 3 4 4 5 5 I need a formula that will go to add value 3+4+5. Only different numbers between itself. Which are my options? Please refrain from multiposting, you got at least one answer already in the worksheet functions ng -- Regards, Peo Sjoblom http://nwexcelsolutions.com <douglasracaixeta@gmail.com> wrote in message news:1146597978.225731.85640@i40g2000cwc.googlegroups.com... >I need one function that adds values that are not equal. > > Like this: > 3 > 3 > 3 > 4 > 4 > 5 >...

Import external data function
What is the earliest version of Excel that includes the function to import external from an Oracle database? Hi Michael, > What is the earliest version of Excel that includes the function to import > external from an Oracle database? Excel 4, I think, though each version since has done it slightly differently. Excel 4 used the =SQL.REQUEST function, Excel 5 and 95 used a separate addin (via MSQuery) and Excel 97 introduced QueryTables. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk ...

Webservice Function Library in Dexterity
With the new version of Great Plains nearly apon us. I think it would be useful to add functionality to Dexterity to allow developers to call/reference web services directly. In the same way as you have a COM_ libarary. Perhaps a WEBSERVICES_ library would also be useful. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader a...

Using xlDown as a function?
I'd like to append data to the bottom of an existing spreadsheet, I have a macro that will import the data, I just need to know where to paste it. Can I use something like (((Selection.End(xlDown))+1).Select to select the row below the bottom? John KBS I like to pick a column that always has data and do this: dim DestCell as range with worksheets("sheet1") set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with somethingthatyouneed.copy _ destination:=destcell John Sutton wrote: > > I'd like to append data to the bottom of an existing ...

Is there an interpolation-function in Excel to read a chart more .
Your question appears to be incomplete. It is usually best to elaborate in the body of your post. The worksheet functions FORECAST(), TREND(), and GROWTH() can be used to interpolate linear, multiple linear (including polynomial) and exponential regressions. Jerry ...

Sub-Total and VLookup problems
I have correctly set up both sub-total and Vlookups in various spreadsheets, but these functions are not working properly. The sub-totals (using sum function) adds to zero, and the vlookups do not recognize matches that I know exist. I've consulted many people, and all do not have an answer. Can you help? Hi sounds like an format issue. Probably your values are actually text. What does the following formula return =ISNUMBER(A1) Where A1 is one of your value cells Also if you think you have a match check it directly with =A1=X1 and see if this formula really returns TRUE. -- Rega...

TIME function used in Excel #2
I'm new to these newsgroups, so I apologize if my etiquette is not appropriate. I'm trying to help a friend whose computer was recently replaced. In his new computer, his custom macro will not run. He gets a "Microsoft Visutal Basic Compile error: Can't find project or library" with the TIME function highlighted. I first suggested using the NOW or DATE function, but he said it creates an additional series of errors. I also tried installing 'Windows Script 5.6' to no avail. Any suggestions on how to get this TIME function to work? Thank you in advance for an...