vlookup & additional formula lookup questions

ace03110

New Member
Joined
Oct 22, 2006
Messages
24
This is my first post on this messageboard, so I look forward to any help that I can get.

I read the posting guidelines, so I've got to say up front, I unfortunately don't know what version of Excel I'm using. Though I have some degree of skill with excel, I certainly don't know about a lot of it's ins and outs and capablilities, so perhaps there are some simple answers to my questions below that I simply am unaware of.

At any rate, here are my two questions explained as completely and clearly as I can:

1) I'm using a vlookup in one spreadsheet where I've got a large listing of trial balance account number. Then, in another spreadsheet, I'm copying and pasting trial balalnces with the TB account numbers and figures for each of those accounts. These account balances get pulled into the other spreadsheet via the vlookup, however, not all trial balances have all of the acct numbers, so in this instance, the other spreadsheet, when trying to do a vlookup for that account, obviously can't find it and inserts "#N/A" into the cell. This causes errors in other formula's that I have on that spreadsheet, so, I'm wondering how I can get the vlookup to insert a numeric zero into the cell rather than the "#N/A".

2) Here's the 2nd question....I'm trying to convert a word document which has different numeric figures in it into an excel document where it can pull these figures, so what I was thinking was copying and pasting each of these paragraphs of sentences into single excel cells. I'm wondering if there's a way that I can somehow have say "IF(then)" formulas within the body of this text, all of which will be in a single excel cell, where the "IF(then)" formula's pulls numbers from other spreadsheets within that workbook? If this is not possible, which I've thought it could be, is there any suggestions on alternate methods to get the same result without putting each word from these sentences and paragraphs into individual cells?

Lastly, I have a simple question about something that happens that's funky....I have all of my print area's clearly defined in various spreadsheets, but for whatever reason, the first time you go to print some of these sheets, the data on these spreadsheets is either elongated or compressed so that it's not printing it in a readable format. If you reprint it even one second later, it comes out fine, as was intended, so all it takes is hitting that print key a 2nd time, however, it's simply an annoyance and a waste of paper. Any ideas on why this occurs and what can be done to avoid it?

to whoever is able to give some advice and help on the above, I truly appreciate it in advance and thank you for welcoming me to this board.

thanks,

Terry
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
For your first query, you can wrap the Vlookup in an IF function, together with the ISNA function. e.g.

=if(isna(YourVlookupFormula),0,YourVlookupFormula)

You can replace the zero with whatever you want.

So if the VLOOKUP returns #N/A, it will display as zero.
 

ace03110

New Member
Joined
Oct 22, 2006
Messages
24
Excellent...I'll give it a shot in a second. I tried a number of variations of If(then) formulas, but none worked. I don't know what an ISNA funtion is, so after I input that and see that it works (which I'm assuming it will), I'll try and learn some more about it. Thanks alot JohnnyTightlips!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ace03110
Welcome to the board

If I understood well your second question you want to concatenate text with cell values.

An example you have in word.

The sales were $1,111,111 in 2003

You want to have that text in excel but the number to be pulled from a cell. You can use

Code:
="The sales were "&TEXT(A1,"$#,##0")&" in 2003"

If A1=1234567

excel will display

The sales were $1,234,567 in 2003

Is this what you want?
PGC
 

ace03110

New Member
Joined
Oct 22, 2006
Messages
24

ADVERTISEMENT

Hi PGC01, and thank you for your suggestion.

It kinda worked...well, it definitely worked for the numbers. What if I want to pull in a percentage or a date? I tried some substituting within the formula you sent, and on the date one, the best that I could get say for "August 31, 2006" was "31at2006". What does the zero in the "(A1, "$#, ##0") poriton of your formula signify?

Again, thank you for your reply as I'm definitely on the right track, but need a couple more tidbits to finalize it.

Terry
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Terry

You are right. The example I posted is for numbers. The

Code:
"$#,##0"

in the TEXT function is the format you want. It is a currency format, dollar sign plus a number. The # displays only significant digits, it's there only to specify that you want a comma as thousands delimeter in case a number is bigger than 1000.

You have to define the custom format for each data type.

If you want a percentage with 2 decimals (12.34%) you would use:

Code:
"0.00%"

or a date like "August 31, 2006" you would use

Code:
"mmmm d,yyyy"

or a datetime in standard format (like "2006-08-31 14:34:21")

Code:
"yyyy-mm-dd hh:mm:ss"

There are too many possibilities to explain them all in a post.
The best is the help "Create a custom number format".

Also

http://www.ozgrid.com/Excel/CustomFormats.htm

for a brief explanation and a table with all the formatting characters.

Hope this helps
PGC
 

ace03110

New Member
Joined
Oct 22, 2006
Messages
24

ADVERTISEMENT

Worked like a charm. Thanks alot PGC01...I really thought that I couldn't do it, but took a shot here. Sometimes after I've done it, it comes back with a "Formula too long" error message. So, with those, I've just been cutting sentences out and pasting them in the next cell. I will take a look at the link you supplied as well.

Now, if anybody has any clue as to why that printing error occurs, I'd be 100% all set (for now anyways).

Thanks again to all who helped w/my problems.

Terry
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

I'm glad I was able to help.

I cannot help you with your printing problem but I think that with the title of your post you will maybe not get the attention of members of the board that could help you.

As you know it's part of the guidelines of the board that you should not open a new thread with the same problem. However, in this case, your third problem is of a complete different nature from the other 2 and so I suggest that you open a new thread with a title more appropriate, something like "Problem when printing first time" or something else that you think fit.

Cheers
PGC
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You might also try this for Question 1.

=IF(VLOOKUP(range,value,collumn)="","XXX",VLOOKUP(range,value,collumn))

Replece XXX with whatever you want displayed instead of #N/A.
 

ace03110

New Member
Joined
Oct 22, 2006
Messages
24
Hello all,

For my 2nd question, though the provided solution and formula works great for small sentences, I continue to get the "Fromula too long" error messages, even if it's not really long. In other words, I've got paragraphs that I can't really split up any further for presentation purposes, but need to work these formula's into the text to pull specific info and insert it. Does anybody have other suggestions or formulas that I can use to get around this error?

thanks in advance for any helpl provided,

Terry
 

Forum statistics

Threads
1,136,366
Messages
5,675,355
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top