At Excel Tip, we make it our aim to make you excel in Microsoft Excel by providing you with the help of the best- in- the- field experts.

With our community of Excel Experts you can get a fix/solution for all your basic and advanced excel needs with just one click of your mouse.

Excel Tip is a sister concern of Excel Forum (www.excelforum.com), which is the world’s largest Excel expert community with close to 1 Million registered members. Our community has an ever-growing network of MS Excel specialists from around the globe with ~500 members joining us every day and monthly visits by a whopping 1.7 Million to 2 Million visitors.

Combined together, Excel forum and Excel Tip can solve even the most complex of Excel queries that you might have.

We can assist you in all your Excel related queries and difficulties. Be it the matter of creating interactive spreadsheets or designing complex dashboards, you name it we have it.

So, what are you waiting for? Join our dynamic community today and Get Better at Excel!

We love to hear from you. You can get in touch with us at [email protected] for anything and everything.

When inputting a birthdate that is before 1/1/1930 (i.e. 12/31/29), excel defaults the year as 2029. Why is this and is there a fix or a setting?

Excel makes many assumptions in the background as an attempt to improve the user’s productivity. These assumptions often take statistical probabilities into account. The assumption engine arbitrarily, but based on probability, assumes that dates prior to 1930 are unlikely. The only workaround I am aware of is to type the full four digits of the year in question. Incidentally, if you type only the month and day (Jan-01, for example) excel assumes that the date is in the current year.

“You can calculate a persons Age in years using:

=DATEDIF(A1,NOW(),””y””) “

“Hi Chris,

That is nothing to do with Excel directly.

You have your computer set up to make that assumption, and Excel has followed your instructions accordingly.

Check out your date settings under Regional Settings.

Alan.”

“From Excel help:

When you enter a date in a cell and you enter only two digits for the year, Excel interprets the year as follows:

The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.

The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.

If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can, without the assistance of your system administrator, change the way two-digit years are interpreted.”

“Dear Sirs,

By using the formula =INT((TODAY()-A1)/365.25), I am getting a whole number as age (e.g. 40).

Now if I want to have the number of month also (Years+Months e.g. 40.3) and when I want to merge with word, the same age will appear with one decimal only (e.g. 40.3) and not fifteen (15) decimals (as 40.29847562534987)

Which formula I have to use.

Many Thanks.”

Regarding getting a person’s age in Years, Months, Days, I’m not sure if this solution has been offered, but I’ve found that this formula works quite well:

=DATEDIF(A1,TODAY(),”Y”)&” Years, “&DATEDIF(A1,TODAY(),”YM”)&” Months, ”

&DATEDIF(A1,TODAY(),”MD”)&” Days”

I sometimes prefer using a slightly altered (and shorter) version to help narrow the column width. The shortened formiula reads as:

=DATEDIF(A1,TODAY(),”Y”)&”.”&DATEDIF(A1,TODAY(),”YM”)&”.”&DATEDIF(A1,TODAY(),”MD”)

Hope this formula is a help to at least one person.

“Hi Rebouche,

Please can you clarify a little.

The problem is that a month is not a well defined period of time (can be anything from 28 days to 31 days inclusive).

Therefore, it is difficult to use a ‘month’ as a measure of time (as opposed to an absolute time reference for which it is very well defined).

The same problem arises for a ‘year’ – as discussed above. A year can be either 365 or 366 days, and is therefore, not well defined either as a measure of time. What exactly would you mean by 0.3 of a year?

Excel has functions that will ‘standardise’ those period, and resolve the issue that you are encountering by assuming that every year has 360 days, and every month exactly 30 days. See Excel help on the DAYS360 function.

In general, I would suggest that you steer clear of representing someones age as “”40.3″” if at all possible. Giving the age to three significant figures, implicitly leads the reader to assume a level of accuracy that may not be borne out of you ask a selection of people what it means.

If you have to do this, then you need to define your units explicitly for the reader of the report (and other users of the spreadsheet model) before you start so that everyone knows exactly what you mean.

Does that help? Post back if you want more help, but I suggest that the 360 day year has to be the way to go for you, even though it leads to errors close to a person’s birthday.

Alan.”

“Hi Alan,

Many thanks for your answer.

It is mail merge between Word and Excel, and my target is to select people using age with one decimal only (as mentioned already).

For example, When I am inserting the date of birth in A1(which is = 14 June 1962) and in B1 (=NOW()) and in another cell, I am using the follwing formula: =IF(A1=0;””””;((B1-A1)+1/365). I am getting the following result 41.25018031 formatted in number with one decimal only 41.3.

The problem is when I am reporting this age to my document (Word) the result is as follow: 41.2501533878744171 but I need only to have 41.3 nothing else … Can you help to resolve this problem by keeping at least the same formula mentioned above.

Thanks “

“Hi Rebouche,

If you change your formula to:

=Round(IF(A1=0;””””;((B1-A1)+1/365),1)

then this will change the actual value to be rounded to one decimal place.

Does that work for you?

Alan.”

“Hi Alan,

Unfortunately, it does not work with me.

Many thanks for your assistance.

Rebouche”

“Hi Rebouche,

You are right – it doesn’t!

I copied your formula from your post, and just put the ROUND outside of it. I should have done it in Excel and pasted to here:

=IF(A1=0,””””,ROUND(((B1-A1)+1)/365,1))

Does that work?

Alan”

“Hi Rebouche,

I am not familiar with mail merge, but perhaps someone else will be able to help you with why it changesthe value back to fifteen decimals.

The value returned by the formula will either be “””” (blank) or will be a number to one decimal place.

However, as a workaround, you could perhaps copy and paste the answers to values, thus removing the formulae, and leaving only the values (to one decimal place), before you mail merge.

Would that work for you?

Alan.”

I’m attempting to write a formula to lookup up a value from multiple ranges 4 unique tables. I would like to return #n/a if the lookup value is not contained in any of the four tables otherwise if it is contained I would like the formulat to bring back the expected value. I understand vlookups but I’m a bit confused in terms of writing one for several ranges. Please help asap!

“The *easiest* was to do this, and make it understandable for others to follow, would be to do four separate lookups in a calculation area, and then do you main lookup on that calculation range.

That way each lookup is relatively simple.

You will need to decide on a policy if the items appears more than once, but that is always the case for lookups. “

“Hello i’m, kinda new to Excel and i’m trying to make a spreadsheet for my work, i require it wo calculate the amount of days i have worked, applications i have processed in a day and how many hours i have worked a day, now i have set the spreadsheet out and i have my average formulas working fine. But when i have a ’0′ in the day field ( meaning i have worked no days! ) & a ’0′ in the Applications processed field – i require a ’0′ to appear in another coloumn i have ( Average Process’s per Hour )

When i have this i will be able to work out the Average Applications i have processed per hour in the working week, can anyone help?

( Hope that all makes sense too ) “

“You do not say exactly what the problem is, so I am going to guess that you are calculating an average and either imlicitly or explicitly dividing by zero at some point.

This will give you the #Div/0! error.

To avoid that, you can error trap your answer by using something like the following:

=if(iserror(Average_Answer),0,Average_Answer)

Average_Answer can be either your actual formula or a reference to the cell where it is.

Hope that helps, “

I am trying to find a number in a list (sorted in ascending order) closest to another number. Using LOOKUP functions don’t work because it finds the closest match LESS THAN the number I’m comparing it to. For example: In a list of numbers 1-10, if I am looking for the number closest to 5.9, the answer should be 6. But the LOOKUP function returns 5 because it is the closest number less than 5.9. I think an array formula would work somehow, but not sure how.

Have you tried MATCH?

“Alan — Yes. Match was tried. I have since found a way to do this. See url

http://www.cpearson.com/excel/lookups.htm “

“I’m trying to understand the need for array formulae but find that any example I see could be performed without.

You could do the same as the example at the top using sumproduct.

Has anyone got any examples that couldn’t performed another way (and in the same number of steps). “

“The problem with SUMPRODUCT is that it is not a standard Excel formula.

It relies upon an add-In, which is fine until someone doesn’t have that particular add-in at which point it all turns to custard with the beautiful model you have built, and credibility hits rock bottom.

For a further example, how about the situation where you had N columns of data that can be used to categorise sales (product type, location, customer, sales person, time of day, promotion code etc etc) and a further column of values being the actual sales.

An array formula can let you quickly, and very simply, sum whatever you want out that data set using criteria that might be pulled from another part of your model (and not just exact matching criteria – you can set boundries too), without having to resort to manual approaches such as pivots or filters (I am ignoring the fact that you can ‘automate’ almost anything using VBA).

However, I know what you mean. I looked at arrays years ago, and could not see the point. Once I actually understood what they could do however, I began using them in almost every situation because of the ease of doing things that would otherwise take complex formulae and lots of them. Its like a light coming on I suppose.

Hope that helps, “

“Cheers Alan, starting to see the point from that.

I’ve got a colleague who must have turned the light on as all they go on about lately is array formulae.

Aren’t they a bit awkward though when producing a model for another end user in that they only have to go in to edit the formula and hit enter without holding down the correct key combo and it’s stuffed.”

“Yes, that is a risk, and I guess you can only stop that by using protection

That is really only a risk with an array formula in a single cell though, since it it is spread across two or more cells, they all have tobe edited together (obviously).

Most novice users, and particularly those that don’t even know what an array formula is, will not even manage to edit the formula(e) at all in that situation.

Also, if they do just ENTER the formula, it is not really stuffed (damaged), you only have to go back and enter it properly to fix it. No permanent harm done is what I mean. However, the result could shag the model if no-one notices of course!

I uess it is like many other things in life – pros and cons, and you have to decide where the balance lies.

BTW, check out this discusion for more on arrays – perhaps you can help out with generating the matrix array if we haven’t already got the solution when you read this:

http://www.exceltip.com/st/vn/125.html “

What you get there? Why it change after pressing F9 for example?

“Is is possible to use advanced filters in a shared workbook? I thought I had it working and invoked it with a macro, but it seems not to be working anymore. Is this just not allowed in shared workbooks?

Thank you,

Lou”

F9 will cause your spreadsheet to calculate, if it is set up to calculate manually. To change to automatic calculation: Tools\Options\Calculation

When using the advanced filter to copy to another location, is there any simple way to bring the comments over as well? The advanced filter is such an amazing feature, but if I can’t bring the comments over too, I might as well do it all another way in VBA anyway, bummer, I was soooo happy about all this

I have 2 columns with a start time and finish time. However, the columns are both in ‘number’ format (to 2 decimal places). How can I calculate the time difference in minutes? Can anyone help me ?

“Hi Craig,

I will assume that you mean that the columns contain date / time values to two decimal places.

If so, subtract one from the other, and this will give you the time difference in days (Excel’s native unit of time).

You can then format the rsult to display this answer in minutes by using a custom number format such as:

[m]“” mins””

This will *display* the answer in minutes.

Hope that helps,

Alan”

I have done a daily time sheet in excel, now when I try to total the Total worked hours for a month, it just does not seem to work.. can u help

“Hi Damien,

First thing I would check is your units.

Are you recording the time in standard Excel units (being days = 24 hours)?

If not, then when you try to add them togather you may get odd results.

Note that you can format the cells to *show* your time in hours – that is a separate issue from what the cells contain though.

That does not mean you cannot use hours or any other units you like, but if you do, you need to be very careful throughout your calculations.

Alan.”

“Anyone know the formula for calculating Hrs between start tme and end time to come up with hours worked less 30 minutes for lunch? ie, Start Time =9:00 A.M End Time =5:30 P.M. This should = 8 hrs.

Would appreciate your help! Thanks”

“Anyone know the formula in excel for calculating Hrs between start time and end time to come up with hours worked less 30 minutes for lunch and have the results for each employee you add total up in one cel?ie,

Employee #1 Start Time in B8=9:00 A.M End Time =5:30 P.M. This should = in B7 8 hrs. Employee #2 Start Time in B9=9:00 A.M End Time =5:30 P.M. This should = in B7 16 hrs.Employee #3 Start Time in B10=9:00 A.M End Time =5:30 P.M. This should = in B7 24 hrs. And so on, as I have 35 employees. Would appreciate your help! Thanks”

“Search for this tip at this web site:

Add time value exceed 24 hours in Microsoft Excel “

“I don’t see the [m] format in my custom formats.

I am still finding it difficult to establish an hourly rate formula when an operator works (for example) 1:48 and produces 50,000 units. I can tell the system to add 60 + 48 to get 108 minutes and then procede, but how can we calculate if there are 3, 4 or 0 hours plus minutes possible? (IE: 3:06 / 4:42 / 0.35)”

“Hi Tom,

If you have the time spent in column A, and the units in column B as follows (A1:B4):

1:48 50000

3:06 85000

4:42 113000

0:35 17000

I am assuming that the times are entered as times (not as text for example).

Then you can calculate the average units per day (being 24 hours) as follows in column C:

=B1/A1

You may have to format the answer to General (for example) since it will probably auto-format as a time.

In this example, C1 = 666666.666… units per day (24 hours).

If you want it per hour, then divide by 24.

If you want it per minute then divide by 1440 (24*60).

Just watch your units and you should be fine.

Alan.”

“If you don’t see a custom format already listed, just type it into the box above the list.

Alan.”

Need to combine date into one column so that other calculation formulas can handle them for subtraction from other dates & times to yield hours and minutes between two dates. Help—diane

“Hi Diane,

Assuming that your two columns (date and time) are already ‘clean’ and are in A1:B4 then you can just add them together:

C1 = A1 + B1

However, for this to work, your dates need to be exact dates, not dates with times (in other words the date values are integers), and the times need to be pure times, with no date element (in other words, the time values need to be greater than or equal to zero, and less than 1).

HTH,

Alan.”

“Thank you, Alan, however the tip you shared did not yield the desired outcome.

A B C

1 4/11/2003 07:46

2 4/11/2003 07:46 4/11/03 7:46

I have data as seen in line one, and can not find a formula or method to combine the date & time into ONE column without losing data. In line two here, I show my desired product. End goal is to determine time intervals between different DATES AND TIMES. TQM to the max! desperate!! Anybody???”

“Hi Diane,

I just tried it, and it works perfectly for me in Excel 97 and Excel 2000.

You do not indicate *why* or *how* it doesn’t work, but I would gues the most likely cause it that either your dates or times do not meet the criteria I outlined above.

If you clean up the two ‘input’ columns first, does it then work?

Alan.”

WHEN I OPEN THE WORKSHEET I WANT THE WORKSHEET DATED, BUT WHEN I SAVE THE WORKSHEET I DO NOT WANT IT TO CHANGE THE DATE LATER WHEN I OPEN IT AGIAN.

Is there anyway when doing a Vlookup, that if the Vlookup value doesn’t exist I can somehow stop the value reading #N/A. Can I make it be 0 or blank ?

“Read the tip:

How to change error result calculation when using Vlookup formula to 0 or empty cell in Microsoft Excel “

Where do I find this tip to change the error result calculation in a vlookup so it doesn’t read #N/A ?

copy the tip titles words into the search box at the upper left side and press on “search site”

“Use the IF function.

The logical test is

ISERROR(VLOOKUP(“”whatever””)).

If the vlookup produces an error, have the IF function produce a zero or blank cell.

If the vlookup doesn’t produce an error, have the IF function execute the vlookup.

Here’s a sample of the complete IF statement, producing a blank cell in the event of an error:

=IF(ISERROR(VLOOKUP(“”whatever””)),””””,VLOOKUP(“”whatever””))

Substituing a set of valid VLOOKUP arguments for the “”whatever”” “

Can we use an “If” function within a “vlookup” function to select the result to be displayed?

“If I understand your question correctly, I think you would not use the if function in vlookup, but rather the other way around. (Vlookup in If):

IF(Vlookup(item,range,columns,0)=ABC,do something,do something else)

If your vlookup finds the data you want (ABC), you can then instruct excel to execute some function or return a particular value. If it does not find ABC, instruct it to do something else.”

In some way I have problems using VLOOKUP when I need to specify a column number from a differnt worksheet. How do I specify a different worksheet/column number? Do I have to specifiy the other worksheet once again after doing so in the “range”? What´s wrong…

is there a way for vlookup to read down the list of numbers and if your number is not the exact match, return the larger of the two numbers that the number falls between?

I have a list of companies, like 8000 companies and i need to eliminate the companies which have the words electric or medical or mesh in their titles how do i do that? E.g excel should go through my list of companies and say ok ABC Electronic Reader -> delete.

I’m trying to change a column of dates in MMDDYR (Year is only two digits) into a column with the year as four digits, any suggestions, PLEASE.

i have an assessment task due soon for my school work, and i cant ge it completed. i need to use the VLOOKUP function to allocate points to cells. i have a reference table made already, determinng the points needing to be allocated, but i cant get it to work.. someone please help me.. im desperate

I have an assignment to distinguish between FIXED and SLIDING scales, describe how they function, which is better and risks of both. PLEASE LET ME KNOW OF ANY INFO ABOUT THEM,AS I AM BATTLING TO LOCATE ANY!! AND AM TIGHT 4 TIME.

“That is exactly the kind of behaviour that Excel’s VLOOKUP function will exhibit unless you eplicitly tell it not to.

Have you read through the Excel help topic in detail? If so, and you need further help, post back with a detailed example of what you want to do, and I’m sure we can help. “

how do i return something different i.e 0 in a vlookup formula rather than N/A when it cannot find a match

“One way is to use an error trapping formula.

For example, if A1 contains your VLOOKUP

A2 could contain:

=IF(ISNA(A1),0,A1)

That will giv zero if the VLOOKUP returns N/A.

You could also replace both A1s in the formula above with the actual vlookup, but that would get both messy and rather long perhaps.

Hope that helps, “

i placed my query here the other day, and am desperate for replies! Alan???!!! Please, regarding FIXED and SLIDING scales with VLOOKUP. Thanks

“I have to admit that I don’t really understand your question.

Are there no boundaries to the scope of your assignment / studies?

Sorry if I am being dim! “

“I managed to work out what the ‘scales’ are, they are not types of ‘functions’ just different ways of working with the VLOOKUP function – thus I was unable to get any info relating t them. But managed to figure them out by using examples etc. Thanks though.

Bridget”

“I’m trying to use the VLOOKUP and LOOKUP function to ref. a value on another sheet. I’ll telling it a name ref’d in my output table and having it lookup a value in my reference table. It is working except for the fact that it is returning the value in the row above the actual value. Example:

Name/Age

Bob Smith/29

Jim Peson/21

Tom Gross/35

I want it to seach for “”Tom Gross”” and return his age (35) but its searching for “”Tom Gross”” and returns “”21″” instead (the value in the row above it). What’s going on? I’ve tied LOOKUP and VLOOKUP and they both do the same thing”

“Difficult to say why it isn’t working since you haven’t given the formula you are using.

However, VLOOKUP should be fine.

If the table is in A1:B4 (title row included), then:

=VLOOKUP(“”Tom Gross””,A1:B4,2) {=35}

Does that not work for you? “

“I need help i have been working on a multipage spread sheet. I want to enter a product number into B18 B19 etc. and then i want it to look through page KS1 column A to find the matching product number and to bring over the name of the product from Column B. Here is the formula i used.

=IF((B18>0),LOOKUP(B18,KS1!A1:B4000),0)

The problem is that it brings the wrong product to the page. I have tried a few formulas but they all do the same thing. Please help “

“Try this:

=IF(B18>0,VLOOKUP(B18,KS1!A1:B4000,2,FALSE),0)

I suggest you put the FALSE on the end, since a ‘close enough’ lookup for product numbers is probably not a good idea and may be the cause of your problem.

Does that work? “

“Alan thanks for the help. That formula seems to work better then mine but it shows NA for the description of the products when i get to the product number on row 100 and beyond.

I spaced the first hundred double space and then i started single space i don’t know if that has anything to do with it. That is the only difference i found. Also there are 24033 rows so i dont’ know if there is a limit?

If you have any more suggestions PLEASE let me know. “

Something i have noticed is that the first 100 entries have darker lines outlining them. Like a border. The items on the bottom of the list are a different font and don’t have the darker lines. Maybe that is something effecting the outcome???

“The formatting should have absolutely no impact on the results so we can eliminate that as a possible issue.

In your formula, did you make the lookup destination range absolute:

KS1!$A$1:$B$4000

If not, then when you copied down, it probably left your destination data out of sync, and hence could not find some codes that should be there.

Does that fix it? “

It’s working correctly now. The problem was the names in the reference table weren’t exactly the same (2 spaces at the end) as the names beening searched. Once I fixed that it worked like a charm.

It is still working until i get to Product 100. I had cut and pasted from row 100-24333 so it is in a different format i think. Maybe that is the problem? But thanks for the formula before it wasn’t even doing the first 100.

“Is there any way to do a lookup and pull multiple rows of information based on the same lookup value?

For example I have a monthly sales spreadsheet by customer number and I want to pull a specific customers information to another spreadsheet. This customer has multiple purchases during the month.

Is there anyway to do this?”

“I am trying to lookup values from multiple pages of my spreadsheet and I cant figure out how to write the formula.

=IF(B18>0,VLOOKUP(B18,KS1!A:B,2,FALSE),0)

ks1 is the first page and i want to look at page Lf1 and lf2 etc. the format is the same on all pages just different brands of products. “

“I think fro your description, that you should look at using a pivot table.

They are very useful for summarising a dataset into a logical layout, and are extremely flexible.

Have a look, and post back if you need more help. “

“Glad to see that it worked above.

As you noted, it is very important to be aware of the actual structure and form of the data that you are analysing. Those leading and trailing spaces make a big difference to a computer!

Regarding lookups across different sheets:

I normally suggest to people that they create a new sheet, and popoluate that with separate lookups (perhaps one column per data sheet) that effectively summarises the larger dataset.

That way, the formulae in the new sheet are all relatively simple (normal lookups on single sheets).

Then you can create a single (simple) lookup on your final output sheet that references just the single intermediate sheet.

It also has the benefit that if you or someone else is trying to understand it in three months time, you will have some hope of seeing what is going on!

HTH, “

Can you have nested Vlookups where the inside function is returning a Named range for the outside function to use? Excel returns #NA.

“You can certainly nest VLOOKUPs although as per my reply above, I suggest simplicity for each cell is also important, so you might be better splitting them out in a calculation zone.

However, aside from that, you talk about a VLOOKUP returning a range for another VLOOKUP to use.

The normal output from a VLOOKUP would be a value derived from a cell in the destination lookup table.

I cannot quite see where you are going if you are looking for VLOOKUP to return a range? “

I am developing an input sheet to define which external data table should be used in a subsequent lookup routine. Can the ultimate range name be constructed by concatenating the result values from independant Vlookups? Informed responses with email addresses are appreciated.

“You can construct the lookup range from parts as for any other formula using the normal INDIRECT function, which may utilise the results of other formulae including VLOOKUPs.

If you post a more specific example, I’m sure someone will offer you an ‘informed response’

On a separate note, I would encourage replies to be posted here rather than via email for two reasons:

1) Everyone else benefits from the discourse, and the ExcelTip community can learn from each other; and

2) If you post your email address anywhere on the internet, you may as well wave a big red flag saying, “”Please spam me!””. Personal choice of course.

Hope that helps, “

Alan…Thanks. I appreciate your insight and guidance.

“I have a vertical column (A) of reference text data in Excel 97.

I have a separate set of 5 columns of data (B,C,D,E,F) within the same worksheet, and only column F is known to possibly contain the reference data from various cells in Column A.

I want to search each of the cells of Column A text data on a one by one basis, and then search for each of those pieces of data within Column F. If I find the data in say row 25 of Column F, then I want to change the format of the found data by carrying out a “strikethrough” and “colouring” of the text contained within the same line i.e. B25, C25,D25,E25,F25.

I have found no examples of conditional formatting and Vlookup. I have also experimented with VLookup but find that the formula seems to want a particular data “value” rather than what data exists within the “referred to” cell.

Can anyone guide me on this please?”

“If your data looks like this (A1:E3):

Alan 20 75 87 Eric

Bob 79 64 88 Doris

Charles 12 10 13 Alan

Then you could add another column on the right (or indeed anywhere else you like, but I would put it to the right), containing the following formula in F1:

=COUNTIF(A$1:A$3,E1)

Obviously you will need to adjust for the actual number of rows and columns you have.

This will generate a zero if the item in Row E is NOT contained in Column A, else you will get a number greater than zero (if Column A contains only unique entries, you will not get anything more than 1).

You can then use the results in that column to conditionally format the other cells (B:E – or whatever).

Hope that helps, “

“Thank you for your quick response.

The data is UNIQUE.

Using your example (table below) and your suggested solution, when I search B1:E7 for ‘Charles’ and “Tom” (found in Column A) in the following table …

Charles Alan 20 75 87

Tom Bob 79 64 88

Charles 12 10 13

Bill 45 33 21

Joan 34 22 10

Noel 56 44 32

Tom 12 1 22

it changes to:-

Charles Alan 20 75 87 1

Tom Bob 79 64 88 1

Charles 12 10 13

Bill 45 33 21

Joan 34 22 10

Noel 56 44 32

Tom 12 1 22

The ’1′ values from the countif condition are located in F1 and F2 and because I have two ’1′ s now, the data is no longer unique. These are on different lines to the data that needs to be struckthrough i.e. B3:E3 and B7:E7.

I cannot see how to connect the NEW values in F1 and F2 to the rows B3:E3 and B7:E7 for a conditional strikethrough. I am only seeing the same problem of “If Charles in A1, Then Search B1:E7 for Charles, and If Found, then Strikethrough the line found e.g. B3:E3.

Sorry if I have missed something obvious here.”

“If you now go to cell B1 select:

Format

Conditional Formatting

Change the drop down to “”Formula Is””

In the formula box, type the following *without* the quotes: “”=$F1=1″”

Note that the column (F) is fixed, but the row is relative.

Select the formatting that you want to apply

Click Ok to get back to the worksheet.

If F1 actually is evaluating to ’1′ then the formatting should now be applied to B1, else nothing will appear to have changed.

Use the format painter icon to copy that conditoinal formatting from B1 to B1:E999 (or whatever the bottom row of your data / formulae is).

That should do it! “

“In one worksheet I’m systems that I’m trying to track.

but I’m missing the data in another field. I have that

data in another worksheet.

worksheet one: fields in Colum B contain my system names. but column c is missing the data. (lots of it.)

worksheet two: field in Colunmn N contains also my

system names but colum I contains the data I need to copy into worksheet one matching exactly those systems.

Now with that in mind what should the syntax be for Match or VLOOKUP to accomplish my task?

VLOOKUP”

“First, to make it easier, can you copy Sheet2!I:I to, say, Sheet2!O:O (or any other column to the right of N)?

If so:

If the data in Sheet2!N:N contains only unique entries, then you could use the following formula in Sheet1!C1:

=VLOOKUP(B1,Sheet2!N:O,2,FALSE)

Once the data is in Sheet1!C:C you can use copy / paste as values to fix it, and then delete the surplus column from Sheet2 that you copied over initially.

Does that work for you? “

“I have succeeded in effecting a conditional format based on the value in column F i.e. ‘1’ values. The $F1 value is looked at and if ‘1’ then the format is applied to B1:E1. However, I forgot to mention that the number of data in column A does not equal the number of data in column B. i.e. I might have 1000 items in column B, and 100 in column A. And so again ~

I want to cross off the 100 items in column B that are originally in column A. But, the 100 items from Column A are NOT necessarily on the same line in column B as in the solution that you have proposed. I think the solution that you have put forward assumes that the data is on the same line and thus I think that the strikethrough is occurring on the same line whenever it sees a NON UNIQUE value of ‘1’ which was established from the countif earlier. I should also mention that my code so far goes as follows:-

In cell F1

=COUNTIF(B$1:B$6646,A1)

If A1 data is found at B123, then F1 converts to 1

The next formula is in B1 and as you suggested is

=$F1=1

The conditional format strikethrough then results in line 1 even though the data was needed to be struckthrough on line B123:E123.

Therefore, I think we are almost there but not quite.

Thank you for your patience with this one. Regards…..Noel”

“No probs – happ to help.

I think we are, as you say, ‘almost there’.

If you have your data as follows (from an example up above):

Charles Alan 20 75 87

Tom Bob 79 64 88

Charles 12 10 13

Bill 45 33 21

Joan 34 22 10

Noel 56 44 32

Tom 12 1 22

This covering the area (A1:E7) but with nothing in A3:A7.

If you put this formula in F1 and copy down:

=COUNTIF(A$1:A$2,B1)

This will place a ’1′ beside rows 3 and 7 (Charles and Tom in column B).

You can now apply the conditional format as discussed above, based on the result in column F.

Note that even if column A contain some non unique entries (two entries for ‘Tom’ say), then you can still use thi method, except that some of those ’1”s in column F will become ’2′ or more.

In that case, just use a conditional format that looks for a number greater than zero (not just ’1′).

Does that do it? “

This has solved the problem. Thank you for your advice – it is much appreciated.

“I am trying to compare some data on two different sheets and then return the value of a different column if they match. Here is my formula.

=VLOOKUP(B2,’Our Status’!$A$1:$A$2123,2,FALSE)

When I gave the column index set to 1 it returns the compared value or #N/A if not matched. If I change it to 2 I still get the #N/A’s but when it is supposed to return for a matched value all I get is #REF. Any help is appreciated.”

“Your formula contains the range (A1:A2123) which is a one dimensional ‘list’.

However, you are asking VLOOKUP to return the value from the second column of that range (which does not exist).

If you change your formula to include column B, it should work:

=VLOOKUP(B2,’Our Status’!$A$1:$B$2123,2,FALSE) “

That worked Alan. Thanks for the help!

“is it possible to use vlookup on more than 1 value

eg a table with times in column a, names in column b and values in column c.

so i would want to check values in columns a and b and return values in column c where the 2 values match. i know it can be done with a pivot table, but i want the spreadsheet to be as automatic as possible, ie no need to refresh a pivot table. “

I am comparing to lists using VLOOKUP. My task is to get phone numbers from the master list, (50,000 names, addresses and phone numbers) transposed to my list, (100 names and addresses). VLOOKUP finds the common name. I need to know how to tell it to “grab” the adjacent phone number as the response to the argument answer of “TRUE”, and enter in the cell where the formula is. Hope this is clear, cause I’m scratching my head. Thanx in advance.

“Suppose you have the following data in (A1:B4):

Apples Red 345

Bananas Blue 43556

Bananas Red 34

Apples Blue 234

Then the following formula will ‘pick’ the value ’234′ from the data:

{=INDEX(C1:C4,MATCH(1,(((A1:A4)=””Apples””)*((B1:B4)=””Blue””)),0))}

Note that this is an array formula – check the online help for more on arrays. “

Sorry – obviously that data is in (A1:C4).

“That sounds exactly what VLOOKUP will do for you.

Have a look at the online help, and post back with a specific example (small data set and your formula) if you can’t get it to work. “

thanks for the speedy reply, could you explain the structure of the formula so i can amend it to my spreadsheet?

“VLOOKUP:

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Syntax

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

The values in the first column of table_array can be text, numbers, or logical values.

Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

If VLOOKUP can’t find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Do you have any specific problems getting it to work? “

I have a problem with my Vlookup when the data in the table is not in ascending order. Do I need to have a macro that sorts the data, any tips?

“If you are using the FALSE parameter, then it won’t matter unless you have multiple results for a given search value (in which case you’ll only get the first one it comes to).

If you are using the TRUE parameter, then you definately have to have the data sorted. “

“This is probably considered a dumb question, but heres my situation.

I have a number (i.e. 350,000) that looks up the number on a separate sheet to give me a value in the same row, different column.

What it currently does is just look up the first 3 digits (i.e. 350,XXX) and gives me the number.

What I need it to do now is round the number to the nearest thousand before it looks up, but not change the original number. Nearest thousand would be 499 and below and; 500 and above. “

“If you want to round a number in A1 to the nearest thousand, just use:

=ROUND(A1,-3)

Does that solve your problem?

Alan.

Reply: re: rounding numbers for lookup function/formula – Jonathan from Bakersfield, CA wrote on September 2, 2003 5:45 PM EST

Alan Posted on: 31-12-1969

Hi Jonathan,

I’m not sure why it doesn’t work for you.

After you applied the above answer, did your formula look something like this:

=LOOKUP(Round(‘Sheet 1′!A1,-3),’Sheet 3′!A1:A1000,’Sheet 3′!B1:B1000)

Basically, you are taking the value in Sheet1!A1, rounding to the nearest thousand, then looking for that (rounded) value in your lookup range (Sheet3!A1:A1000), returning the corresponding value in Sheet3!B1:B1000.

Not sure what the range after the plus sign is doing there, but that is a different issue.

How does it not work exactly? What results are you getting that is not what you need? “

“I’m using the original number (i.e. 350,565) to add to other numbers on a separate sheet….

Maybe if I put my question like this, you’ll understand better, using this formula:

=LOOKUP(‘Sheet 1′!A1,’Sheet 3′!A1:A1000,’Sheet 3′!B1:B1000+’Sheet 3′!D1:D1000)

Instead, I need ‘Sheet 1′ A1 to stay 350,565 but to find the 351,000 reference in Sheet 3 for the correct referece amount..

If the original number was 350,325 then I would need the 350,000 reference. Rounding to the nearest thousandths, I think…

Does that help? “

“I wasn’t putting in the -3… whatever that is for… it works now..

If you weren’t a dude, I’d kiss you.

“

“how do I vlookup 2 datas simultaneously?

For Eg. Main heading has the date and subheading are agent codes.Date column is merged with the agent names below

like Sep-1 Sep-2

A B C D A B C D

Area1

Area2

And in another sheet I use vlookup formula or any other excel formula so that I enter the date in a cell and the agent name I should get the numbers of the agent. “

“As a general comment I suggest you read up on the MATCH and INDEX functions – they look likely to solve your problem.

more specifically, what data are you looking to return from the table you gave? You mention returning ‘numbers of the agent’, but your table layout contains no numbers? “

“Could someon explain how Match and Index could solve the problem of multiple matches? I haven’t been able to figure it out.

I think I’m having the same problem as Iqbal- I’m trying to match up products that sometimes compare to more than one competitor product. I’d like Vlookup to take out all the matches, not just the first one it finds. Any help would be greatly appreciated. “

“This tip doesn’t seem right?

I want to add the tax to the net price”

“100.00-18% = 82.00 NOT 84.75

Try to find the net price:

A1 100.00 B1 18.00% then try the formula=(100%-b1)*al answer 82.00″

Tip is great. We all know how to add VAT onto the basic price, but I hadn’t a clue where to begin doing it the other way around. By the way – VAT is 17.5% in the UK.

I am trying to write a macro to process the comments collection seaching for comments that have text formmatting applied? Ps. Using Win 2000 with Excel XP

I’m sure the tip shown works but I have used a cheap & dirty vlookup on a table that I create. The table has 2 columns, column 1 has numbers 1-12 which represent months, column 2 reads: 1 1 1 2 2 2 3 3 3 4 4 4 I just vlookup the month in whatever source date I’m using to the month in my lookup table and return the column 2 value. Works for me !! J.

“I also use lookup function, but I do not create extra table with month and quarter numbers. I write it directly into formula, for example:

=HLOOKUP(A1,{1,2,3,4,5,6,7,8,9,10,11,12;1,1,1,2,2,2,3,3,3,4,4,4},2)

where A1 is month number

J.”

“Who is in the top, in the bottom?

why I must sort it ?

“

“I, NEED A FORMULA THAT CALCULATES MY DAILY HOURS WORKED. EXAMPLE , I WORKED THE FOLLOWING DAYS / HOURS

MONDAY 10:15 AM TO 5.10 PM , TUESDAY 9:AM TO 4:45PM WEDNESDAY 8:10AM TO 3:00PM, THURSDAY 7:45AM TO 6:15PM & FRIDAY 9:45AM TO 2:00PM “

“I, NEED A FORMULA THAT CALCULATES MY DAILY HOURS WORKED. EXAMPLE , I WORKED THE FOLLOWING DAYS / HOURS

MONDAY 10:15 AM TO 5.10 PM , TUESDAY 9:AM TO 4:45PM WEDNESDAY 8:10AM TO 3:00PM, THURSDAY 7:45AM TO 6:15PM & FRIDAY 9:45AM TO 2:00PM “

“Hello-

There is a tip on the site titled “”Time intervals using VBA in Microsoft Excel

“” – check it here”

Think you for your help, however I need my hand held , I don’t understand how to write the formular, I put in my information in cell a1 & b1. and it did not work. Using the information above, would you please give me a actual example and caluate the daily hours worked.

I can add time together to give the total amount of hours and minutes worked in a week eg 35:30. If I work for $15 an hour how do I calculate this using my time calculation?

“Hi Allen,

If you enter those times in cells C2:D6 and I will assume you use standard (24 hour) notation, then the following formula entered in E2 and copied down should give you what you want:

=D2-C2

Depending on your cell formatting, that can show as a decimal or hours : mins, but fundamentally, the cells contain a number that is the portion of a day in each case giving the following (in hours:mins):

06:55

07:45

06:50

10:30

04:15

or the following (in decimals):

0.288194444

0.322916667

0.284722222

0.4375

0.177083333

If you then sum E2:E6 you would get:

1.510416667 (as a decimal) which is just over 1.5 days or just over 36 hours in total (36 hours, 15 mins to be precise).

You can of course format the sum cell to show the answer in whatever form you prefer (hours, days etc).

For example if you want to display it as 36:15 then use the custom number format:

[hh]:mm

If you want to convert the answers from fractions of a day to hours as units, just multiply by 24 (!).

In that case, the sum total would become 36.25 hours (=36 hrs, 15 mins).

Be aware though that excel would now assume this number is 36.25 days using its standard notation, so you need to take explicit control of the units if you go down that route.

Hope that helps,

Alan. “

“Hi Phil,

If you have the hours:minutes (35:30) entered in cell (B2) and an hourly rate (15) entered in cell B3 then the actual value that is in B2 is (1.47916666666667) as a decimal or (35.5/24) to be precise

Since your units are out of sync (the time is entered as fraction of a day, but your rate is hourly) you need to bring them together.

Let’s convert the hourly rate to a daily (24 hour) rate to give a dollar total of:

=B2*(B3*24) {=532.50}

Alternatively, we could convert the days to hours:

=(B2*24)*B3 = {532.50}

Both of them give $532.50 total of course.

Hope that helps,

Alan.”

“Thanks Alan,

Your solution works well!

Phil”

“I have fields that have time start and another Time End:

I want to know how to have another field that says how many days and hours and minutes have passed.

Start End

1/1/2003 2:00 PM | 1/2/2003 3:00pm”

“Hi Robert,

If both are entered as standard Excel date / time values, then just subtract the end from the start, and you will have a value equal to the number of days that have elapsed.

For example, if the difference is 1.75, then 1.75 days have elapsed, equal to 32 hours.

You can format the answer cell to display your answer in whatever format makes most sense:

For example:

[hh]:mm

would give you 32:00 (hours).

See the reply two above for a more full explanation.

Alan.”

“To close the current workbook the short cut key is Ctrl + W.

This will automatically close the current active workbook”

Is there anyway when doing a Vlookup, that if the Vlookup value doesn’t exist I can somehow stop the value reading #N/A. Can I make it be 0 or blank ?

“Read the tip: How to change error result calculation when using Vlookup formula to 0 or empty cell in Microsoft Excel

“

Where do I find this tip to change the error result calculation in a vlookup so it doesn’t read #N/A ?

copy the tip titles words into the search box at the upper left side and press on “search site”

“Use the IF function.

The logical test is

ISERROR(VLOOKUP(“”whatever””)).

If the vlookup produces an error, have the IF function produce a zero or blank cell.

If the vlookup doesn’t produce an error, have the IF function execute the vlookup.

Here’s a sample of the complete IF statement, producing a blank cell in the event of an error:

=IF(ISERROR(VLOOKUP(“”whatever””)),””””,VLOOKUP(“”whatever””))

Substituing a set of valid VLOOKUP arguments for the “”whatever””

“

Can we use an “If” function within a “vlookup” function to select the result to be displayed?

“If I understand your question correctly, I think you would not use the if function in vlookup, but rather the other way around. (Vlookup in If):

IF(Vlookup(item,range,columns,0)=ABC,do something,do something else)

If your vlookup finds the data you want (ABC), you can then instruct excel to execute some function or return a particular value. If it does not find ABC, instruct it to do something else.”

In some way I have problems using VLOOKUP when I need to specify a column number from a differnt worksheet. How do I specify a different worksheet/column number? Do I have to specifiy the other worksheet once again after doing so in the “range”? What´s wrong…

is there a way for vlookup to read down the list of numbers and if your number is not the exact match, return the larger of the two numbers that the number falls between?

I have a list of companies, like 8000 companies and i need to eliminate the companies which have the words electric or medical or mesh in their titles how do i do that? E.g excel should go through my list of companies and say ok ABC Electronic Reader -> delete.

“Hi,

I’m trying to change a column of dates in MMDDYR (Year is only two digits) into a column with the year as four digits, any suggestions, PLEASE. “

i have an assessment task due soon for my school work, and i cant ge it completed. i need to use the VLOOKUP function to allocate points to cells. i have a reference table made already, determinng the points needing to be allocated, but i cant get it to work.. someone please help me.. im desperate

I have an assignment to distinguish between FIXED and SLIDING scales, describe how they function, which is better and risks of both. PLEASE LET ME KNOW OF ANY INFO ABOUT THEM,AS I AM BATTLING TO LOCATE ANY!! AND AM TIGHT 4 TIME.

“Hi Mike,

That is exactly the kind of behaviour that Excel’s VLOOKUP function will exhibit unless you eplicitly tell it not to.

Have you read through the Excel help topic in detail? If so, and you need further help, post back with a detailed example of what you want to do, and I’m sure we can help.

Alan”

how do i return something different i.e 0 in a vlookup formula rather than N/A when it cannot find a match

“Hi Geoff,

One way is to use an error trapping formula.

For example, if A1 contains your VLOOKUP

A2 could contain:

=IF(ISNA(A1),0,A1)

That will giv zero if the VLOOKUP returns N/A.

You could also replace both A1s in the formula above with the actual vlookup, but that would get both messy and rather long perhaps.

Hope that helps,

Alan.”

i placed my query here the other day, and am desperate for replies! Alan???!!! Please, regarding FIXED and SLIDING scales with VLOOKUP. thanks

“Hi Bridget,

I have to admit that I don’t really understand your question.

Are there no boundaries to the scope of your assignment / studies?

Sorry if I am being dim!

Alan”

“I managed to work out what the ‘scales’ are, they are not types of ‘functions’ just different ways of working with the VLOOKUP function – thus I was unable to get any info relating t them. But managed to figure them out by using examples etc. Thanks though.

Bridget”

“Hey,

I’m trying to use the VLOOKUP and LOOKUP function to ref. a value on another sheet. I’ll telling it a name ref’d in my output table and having it lookup a value in my reference table. It is working except for the fact that it is returning the value in the row above the actual value. Example:

Name/Age

Bob Smith/29

Jim Peson/21

Tom Gross/35

I want it to seach for “”Tom Gross”” and return his age (35) but its searching for “”Tom Gross”” and returns “”21″” instead (the value in the row above it). What’s going on? I’ve tied LOOKUP and VLOOKUP and they both do the same thing”

“Hi Greg,

Difficult to say why it isn’t working since you haven’t given the formula you are using.

However, VLOOKUP should be fine.

If the table is in A1:B4 (title row included), then:

=VLOOKUP(“”Tom Gross””,A1:B4,2) {=35}

Does that not work for you?

Alan.”

“I need help i have been working on a multipage spread sheet. I want to enter a product number into B18 B19 etc. and then i want it to look through page KS1 column A to find the matching product number and to bring over the name of the product from Column B. Here is the formula i used.

=IF((B18>0),LOOKUP(B18,KS1!A1:B4000),0)

The problem is that it brings the wrong product to the page. I have tried a few formulas but they all do the same thing. Please help “

“Validation format cannot be used on cells when sheets are in group mode (severeal sheets selected). A workaround could be as follows:

(put before test on target cell)

If ActiveWindow.SelectedSheets.Count > 1 Then Exit Sub

The correct procedure to apply “

“Alan thanks for the help. That formula seems to work better then mine but it shows NA for the description of the products when i get to the product number on row 100 and beyond.

I spaced the first hundred double space and then i started single space i don’t know if that has anything to do with it. That is the only difference i found. Also there are 24033 rows so i dont’ know if there is a limit?

If you have any more suggestions PLEASE let me know.

Holly “

Something i have noticed is that the first 100 entries have darker lines outlining them. Like a border. The items on the bottom of the list are a different font and don’t have the darker lines. Maybe that is something effecting the outcome???

“Hi Holly,

The formatting should have absolutely no impact on the results so we can eliminate that as a possible issue.

In your formula, did you make the lookup destination range absolute:

KS1!$A$1:$B$4000

If not, then when you copied down, it probably left your destination data out of sync, and hence could not find some codes that should be there.

Does that fix it?

Alan. “

“Thanks,

It’s working correctly now. The problem was the names in the reference table weren’t exactly the same (2 spaces at the end) as the names beening searched. Once I fixed that it worked like a charm.”

“Allan

It is still working until i get to Product 100. I had cut and pasted from row 100-24333 so it is in a different format i think. Maybe that is the problem? But thanks for the formula before it wasn’t even doing the first 100.

holly”

“Is there any way to do a lookup and pull multiple rows of information based on the same lookup value?

For example I have a monthly sales spreadsheet by customer number and I want to pull a specific customers information to another spreadsheet. This customer has multiple purchases during the month.

Is there anyway to do this?”

Yes, we can pull mulitple rows of information based on the same lookup. send example excel file.

Best Regards

Babu

Hello, I purchased the book “Financial Statements.xls A Step-by-Step guide to creating Financial Statements using

Microsoft Excel”, and is a great book.

I’m going to start to study the examples in the book, but my book came without the companion CD-ROM. How can I get the info in the disc? There is a way to download the content?

I really appreciate any help.

Best regards

Ernesto

Hello,

First time i visit your website and it seems to be very useful for excel accounting ninjas like me. I just want to know if a new edition of ‘Financial Analysis and reporting with excel’ is in view and to have some news from the consulting activities of its author.

Thanks for all you’re doing to make excel easy to use.

Best regards from Lille area in France,

STEPHANE NTONGA

hi

would you please help me to download Financial

Statements.xls from exceltips?

Peace! I think this site is affiliated with “www.excelforum.com”. I work for AkzoNobel and I found help to solve a problem in your forum at “www.excelforum.com”. Thank you very much for your help. The =MID() and =LEFT() or =RIGHT() function was pointed out for sorting alphanumeric values and that helped me sort a list. Thank you again. Farewell.

How to find missing values in one range from another range?

Here is situation: i have 13 baseball players. I am trying to put them into a position for each of 6 innings. I put 9 in the field and have 4 on the bench. But then I struggle when i can’t figure out who i am missing for an inning.

thanks for any tips.

Kindly help me on how to download sample financial statements.xls from exceltip.com.

Thanks.

Kindly send me the URL / link for downloading Financial

Statements.xls sample workbook from http://www.exceltip.com as mentioned in your (Mr. Joseph Rubin’s) “Financial Statements.xls

A Step-by-Step guide to creating Financial Statements using

Microsoft Excel” Book.

Thanks and Regards.

Really amazing and it helped me lot. I wish learn more from you:)

Kindly tell me how can I download sample workbook for Financial Statements.xls from http://www.exceltips.com

how can i download sample financial statements.xls from exceltip.com