VLOOKUP and IF formula 05-20-10

I have this formula already in use...

=VLOOKUP(B17,'[DSL UVERSE CLOSE RATES - APR 
10.xlsx]Pivot'!$A$2:$C$291,2,FALSE)

I need it to include an "If" formula as well returning a "-" dash if it 
cannot find the name on b17 in the file it's looking up. 

Any help is appreciated.
0
Utf
5/20/2010 10:27:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
630 Views

Similar Articles

[PageSpeed] 25

=if(isna(vlookup(b17, ...)),"-",vlookup(b17, ...))

if you're using xl2007+, there's an =iferror() that you can read about in
excel's help.

casdaq wrote:
> 
> I have this formula already in use...
> 
> =VLOOKUP(B17,'[DSL UVERSE CLOSE RATES - APR
> 10.xlsx]Pivot'!$A$2:$C$291,2,FALSE)
> 
> I need it to include an "If" formula as well returning a "-" dash if it
> cannot find the name on b17 in the file it's looking up.
> 
> Any help is appreciated.

-- 

Dave Peterson
0
Dave
5/20/2010 10:46:06 PM
Reply:

Similar Artilces:

Desktop Icons 04-23-10
Is there a way to set the background color at the icon name to "clear", or no color at all? I have one computer that does it but I don't know how it got that way. ...

Customizations 02-09-05
CRM product is quiet rich in functionality and covers a lot of business area. Is it possible to customize it to include specific forms and block others.? If yes, could you direct me as to how I should do it? On the subject of templates. I have a letter containing standard contract. The contract has fill in the blank parts like name, date, etc... Can I create a new template containing the format and fixed text of the contract. Also, web forms where I could enter variable part of the contract data. This way I could merge specific data with this contract template and print it. Is somet...

Getting any formula to stop at the next blank space
I frequently write VLOOKUP and SUMIF formulas for my spreadsheets an have found myself typing the same thing over and over. How can I ad in a command that makes each formula stop at the next blank. I als have to make the formula references "absolutes". What I'm wanting t do is copy and paste the same formula down without sacrifices th absolute references -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showth...

Help with formula #9
Can some one help me get this right. Trying to get this formula to start in column b row 3 and continue down. Somethings not working as usual. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngRow As Long Application.EnableEvents = False lngRow = Cells(65536, 1).End(xlUp).Row If lngRow >= 2 Then Worksheets("Sheet2").Range("B3").Formula = "=countif (Sheet1!A$3:A$2000,A3)" Worksheets("Sheet2").Range("B3:B" & lngRow).FillDown End If Application.EnableEvents = True End Sub Your code works fine. A few points: 1. The cell y...

Is there a way to use the previous record value in a formula?
I've created a report to show checks cleared for a certain month and want to place an asterik next to a check number which is out of sequence. Is there a way to use a previous record value in an expression =iif([check no]-????([check no])<>1,"*","") ? On Tue, 4 Dec 2007 10:38:03 -0800, zzamba wrote: > I've created a report to show checks cleared for a certain month and want to > place an asterik next to a check number which is out of sequence. Is there a > way to use a previous record value in an expression =iif([check > no]-????([chec...

GP Alert Formula
I need to create a business alert that contains only certain inventory items. Is there a way to include items based on item number? For example, I need to create a report that includes all inventory items that begin with "A" or "W". ...

VLOOKUP help again please.
I have also finished my workbook. Now I am stumped on another vlookup function. Since some people dont like to download the file I will explain it in words and attach some screenshots. I need to use VLOOKUP and the discount table on the sheet 3 to calculate the relevant discount rate for the discount rate column in sheet 4. I have attempted it as you see in the screenshot, but it returns #NA. I am not sure if this is because my function is wrong or the discount table is set up wrong (I have entered the values such as "21 to 50", and im not sure if excel recognises this means from 2...

Deleting Cell with formulas with Numbers Ex. =2+3 not = A+B
Deleting Cell with formulas with Numbers Ex. =2+3 not = A+B I am trying to create a copy of workbook with only formulas, but some cells exist wit =2+3 which I do Not wanted. How can I delete all of this in a single click (Not Individualy) If you wish to delete *all* numeric formulas, =A+B *AND* =2+3 Try this on a copy of your sheet: <F5> and click on "Special". Then click on "Formulas", and make sure that *ONLY* "numbers" is checked. Then <OK>. You will see that cells containing numeric formulas are selected. Now, just hit <Delete>. -- ...

1099 and V 10.0 upgrade
We are finding that after the V 10.0 upgrade that the new 1099 boxes that were added are not updated with the 1099 amounts that were in our AP records prior to the upgrade. Is anyone else experiencing this? Is there a way to get the 1099 amounts into the 1099 boxes? Is there a sql update that can be done if not through a utility? Thanks, ...

INDIRECT formula not working
Hi, What am I doing wrong here... This formuals does not work: =INDIRECT("'"&"YUNUS"&"-"&A3&"-"&B3&" "&"GD0" &"'!Q1") where A & B are cell references used in the naming of many worksheets I am attempting to obtain the value in Cell Q1 of worksheets: YUNUS-A3-B3 GD0 YUNUS-A4-B4 GD0 YUNUS-A5-B5 GD0 YUNUS-A6-B6 GD0 YUNUS-A7-B7 GD0 YUNUS-A8-B8 GD0 On Thu, 8 Mar 2012 22:27:39 -0000, Yunus <yunus786@btopenworld.com> wrote: > >Hi, > >What am I doing wrong h...

formulas and external data
i have an issue trying to calculate data based on a query i import the data with all my columns and records, at the end i insert a column with a formula that calculates 4 different cells in the row then i run a query to remove null (zero) value data from a certain field, then the formual column that i added manually doesn't look right. for example, if i have 800 rows of data, i copied that formula to all 800 rows in the last column following the external data when i run the query, all the zero values are removed (doing what it should) and the formulas stay, except the last row that is...

USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...

Need Formula Help Copying "Image" URL from B2 to A2
Hi all sorry if this is a repeated question in this ng. I just could not find one related my querry. I have sitmap list in excel. I want to separate the links with the word images in it. http://.........../display.asp?hs=7130g&tariff=tmobxnetflext35.18 http://............/display.asp?image=large&hs=7130g both links are in B1 and B2, I want to copy the the link with the word "image" in the middle to A2. what formula can I use in A2? any tip would be appreciated. Thanks in advance. tim try =IF(FIND("image",B2),B2,"...

VLOOKUP formula (I think?)
Sorry for the repeat question (as I am sure it has been answered) but I am having trouble sorting through threads. Below is my problem/question. I had the following data, for example in a spreadsheet (material, style, price) A B C Carpet My My My $1000 Carpet Sweet Escape $3000 Carpet Red Bud $0 I need to chart on a graph, what data in Column B shows up when a $ value in Column C is present (<$0). The text in Column B is not from a validation list and can vary by thousands of d...

Understanding complex IF Formula within IF formula
I do not know what this formula is doing can you help? =IF(LEFT(QBCash!$B2,5)="Total","",IF(ISBLANK(QBCash!$B2),$A1,QBCash!$B2)) Thanks for your help. DP Hi, The formula is looking at a worksheet called QBCash Breaking the formula down =IF(LEFT(QBCash!$B2,5)="Total","", 1. It looks at the contents of B2 and if the word 'Total' are the first 5 letters of the cell the formula return a null string IF(ISBLANK(QBCash!$B2),$A1, 2. If that isn't TRUE it then checks if B2 is blank and if it is it returns the value of...

Formula Help Please #8
Hello I am using MS Excel 2000. I need a little formula help. I think I need an IF statement. What I am trying to do is =SUM(C58-2000), but I want my destination cell to either what i have above or zero. I can't have a negative number. I know this is simple, but I can't figure it out. Any help will be greatly appreciated. John Kitchens On May 6, 7:18=A0am, "Iron Ax, Inc." <ironax...@bellsouth.net> wrote: > Hello I am using MS Excel 2000. I need a little formula help. I think I n= eed > an IF statement. > > What I am trying to do is =3DSUM(C58-2000)...

Vlookup & Sumif Problem
Help !! I'm trying to combine Vlookup and SumIf (with 2 conditions!) I've attached a sample file with an example of what I'm trying to do !!! Any suggestions would be gratefully received. Cheers Andy File Attached: http://www.exceltip.com/forum/attachment.php?postid=278716 (vlookup.xls) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Your attachment didn't come through. That's good, since this isn't a ..binary newsgroup. Very few regulars ...

Formula?
I'm trying to add a formula in a cell which will leave the cell blank if column A is blank. I use the following: ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")" but I get a run-time error (1004). I know the problem is the quotes in the third argument. I tried replacing the quotes with zero and it enters zero correctly with no error. But I need the cell to be blank, not zero, if column A is blank. Hi Try four quotes instead of two: "=if(+RC[-10]>0,2.5,"""")" -- HTH. Best wishes Harald Followup to newsgroup only please ...

Switchboard 05-14-07
How can I create a switchboard that will allow me to open several excel documents in one folder? I envision a comman button opening a different document, perhaps up to 10 command buttons in one board. Thanks. James On May 14, 10:16 am, Eric Blitzer <EricBlit...@discussions.microsoft.com> wrote: > How about using a combo box with all your excel file names and 1 command > button that will open the one selected. > > > > "Mos.St...@gmail.com" wrote: > > How can I create a switchboard that will allow me to open several > > excel documents in one fol...

1 company will not upgrade to 10.0
One of our companies will not upgrade from 9.0 sp3 to 10.0 sp1. The below messages appear. The following SQL statement produced an error: if exists (select * from sysobjects where id = object_id('dbo.GPS_CHAR') and sysstat & 0xf = 6) drop default dbo.GPS_CHAR ERROR [Microsoft][SQL Native Client][SQL Server]The default 'dbo.GPS_CHAR' cannot be dropped because it is bound to one or more column. ...

Formula help : adding
I a new to excel and as a learning project I am makig a list of my movies. I would like a formula that would add up the cells with text in them. There are some blank cells as seperation for alphabetizing and I don't want to add those cells in my total. There aren't any numbers in the cells and I am struggling to make up a formula. Any help would be appreciated. Thank you Niki look at COUNTA Regards Trevor "Niki" <Niki @discussions.microsoft.com> wrote in message news:825F4C3A-76BF-43DE-92E7-030D99C5C47E@microsoft.com... >I a new to excel and as a learn...

convert the functions and formulas to values
Hi How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste special as value) Thanks Why? Look at Value in the help for VBA Range("A1").Value = Range("A1").Value -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Mireille Abi Nader" <mireillea@newhorizons.com.lb> wrote in message news:7D82E49E-8709-4930-B8D6-7ED311A1F8C9@microsoft.com... > Hi, > How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste ...

Formulas in a sentence....
We have been asked to contact you by our client =c6 , regarding your past due invoice for =c8. What would the formula be? try this ="We have been asked to contact you by our client " & C6 & ", regarding your past due invoice for " & C8 -- Gary Keramidas Excel 2003 "krissy" <krissy@discussions.microsoft.com> wrote in message news:30DC6DFA-2B52-4BE7-8D22-84B25EF7F8E6@microsoft.com... > We have been asked to contact you by our client =c6 , regarding your past due > invoice for =c8. > > What would ...

how to paste formula and retain all or part of the cell references
I have following kind of problem: I want copy a cell from A5 to B5. In A5 I have a formula that takes values from A2 and E5, i.e. "=A2/E5". In the pasted cell the formula changes to corresponding values: "=B2/F5". In some cases this might be what I want, but in current situation I would like to change only column to corresponding - I would liket the resulting cell to include formula: "=B2/E5". How can I do this? Of course, in case of such simple formula it wouldn't be a problem but if I have very long formula with lots of references, it's a nightmare.. A...

Proper formula
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) I have a product list that was typed in uppercase I used the proper formula to change this. I now need to edit the new list but it just gives me the formula not he text in the cell. <br><br>Have tried formatting cells to txt. Any ideas? In article <59bb04bc.-1@webcrossing.JaKIaxP2ac0>, Sands@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > > I have a product list that was typed in uppercase I used the proper formula > to change this. I now need to edit t...