SmittyPro - Witticisms, Help, Tutorials and More

Apr
10

# Excel – IF statements & LOOKUPS

This article will focus on the appropriate use of IF statements, and also show you when it’s better to move into more robust formulas like LOOKUP, VLOOKUP, HLOOKUP & INDEX/MATCH.

IF statements are one of the core formula models you can use in Excel, and they can be very powerful with regards to their logic. Very simply they follow this methodology: IF(something is True, then do something, otherwise do something else).

• =IF(A1=”Yes”,1,2), which simply says if A1 = Yes, then return a 1, otherwise return a 2.

You can also combine IF statements to evaluate multiple criteria by nesting them, however, many people try to get IF statements to do too much. Previous versions of Excel allowed up to 7 levels of nested IF statements, which means that you could evaluate up to 7 different criteria. Excel 2007+ allows 64 levels of nested IF statements, but just because you can do something doesn’t mean you should!

One of the most common uses of IF statements is to return data that matches specific criteria, or falls within a range of values. Here is a relatively common example of a nested IF statement to return letter grades based on test scores:

• =IF(A1>=90,”A”,IF(A1>=80,”B”,IF(A1>=70,”C”,IF(A1>=60,”D”,IF(A1<60,”F”)))))

Another is calculating commission statements:

• =IF(A1>25000,A1*2%,IF(A1>15000,A1*1.5%,IF(A1>5000,A1*1%,0)))

Note that in both formulas, the criteria need to be ordered sequentially in order for the formula to calculate correctly. So in the first example A1>=90 gets evaluated first, and if that condition is true, then the formula performs the calculation associated with that condition. If the condition isn’t true, then it moves onto the second, and so on. But if you get your conditions out of order, then one condition can invalidate the next and render your formula(s) useless. One of the inherent weaknesses with IF statements is that they need to be precise and ordered.

While both of these formulas work fine, they’re unwieldy and should be avoided if at all possible. Why? Primarily because the data in the formulas is static, so if the conditions driving the formula ever need to be changed, the formula needs to be manually adjusted as a result. Granted, the grades example isn’t too bad, because it’s not likely that information will change too often, but just imagine how much work you’d have to do if you have a lot of formulas like the commission example, and you have to change the criteria. Ideally, you’ll get into the habit of only using IF statements for Text comparisons like the earlier example. Yes/No/Maybe or Male/Female evaluations are very common, and the nice thing about them is that criteria aren’t likely to change very often. If you find yourself with situations like this then by all means use IF statements, otherwise it’s time to move up to more robust alternatives, starting with LOOKUP.

One of the primary reasons to move away from IF statements for multiple criteria is so that you can use “table based” reference data. This gives you the ability to have your data points on a worksheet, where the values can be easily changed, as opposed to hardcoded in a formula, where changing the values can be a challenge. Many workbooks have broken because of numerous IF statements that someone didn’t update. In the commission example above, what would happen if you needed to change the 2% and you had hundreds of formulas depending on that one? It wouldn’t be fun, and that’s a relatively small example. Imagine one with 64 conditions! And that’s if you can even find the formula in the first place! Table based dependencies are much easier to change on the fly, which can mean a lot especially if you’re dealing with complex models and testing multiple criteria.

Note: data tables that drive referential formulas don’t need to be housed in the open where users can change them. Very often you’ll find sensitive tables housed on hidden worksheets, where only the creator/administrator can change the data.

The first formula in the family of referential formulas is LOOKUP (they’re called referential formulas because they can return a reference to a value, or a value in a range of values). LOOKUP has plenty of uses, but it’s also the most fragile and least flexible in the Lookup family.

Following are examples of a Discount table, and a Grades table using a standard LOOKUP function:

A LOOKUP formula evaluates a value (either hardcoded or in a cell) then finds a match for it in a table that lists the values to be looked up, and the values to return. In these examples I’m using LOOKUP to return a variable range from a list of values. LOOKUP is governed by the following syntax:

• =LOOKUP(Value you want to look up, range where you want to return the rightmost value).

NOTE: LOOKUP formulas can look up a hard-coded value, or they can look up a value from a cell. =VLOOKUP(900,A2:B23) & LOOKUP(A5,A2:B23) do exactly the same thing, but the latter is much more flexible since you don’t have to change the 900 value whenever you want to change the lookup value. You should always try to use cell references in formulas as opposed to hard-coded values.

So in the example above, =LOOKUP(A5,A2:B23) will look in range A2:A23 for the value closest to what was entered in cell A5, and return the corresponding value from column B. In this case, looking for \$900 results in a 2% result by matching \$750, which is the largest value that’s less than or equal to the value evaluated by the formula. Had you entered a value between \$1,000 and \$1,249 the result would have been 3%. Note that the LOOKUP formula requires your data to be sorted in Ascending order, otherwise it’s not going to provide you with consistent or correct results. A quick note on LOOKUP and its counterpart VLOOKUP is that they can only look to the right of the lookup value; they can’t go to the left. For that you need to combine INDEX/MATCH, which will be covered later.

VLOOKUP – This is the big brother to the relatively limited LOOKUP, and gives you more flexibility with regards to what data you can return and where. VLOOKUP follows this syntax:

• =VLOOKUP(Value you want to look up, range where you want to lookup the value, number of columns to the right of the found value, Exact Match or Approximate Match – Indicated with 0/FALSE or 1/TRUE).

So where LOOKUP is going to return a value from the rightmost column in the range that you specify, VLOOKUP lets you lookup a multi-column range, and choose which column from which to return a value. VLOOKUP is very often used in finance scenarios with 12-Month periods because you can easily choose which month to return.

Note the first two formulas use static column references (2 & 3), which tell the formula to return values from the second and third columns respectively:

• =VLOOKUP(\$A8,\$A\$2:\$M\$4,2,FALSE)
• =VLOOKUP(\$A8,\$A\$2:\$M\$4,3,FALSE)

This is all well and good, and for many models like this one it’s ok, but it can be time consuming to have to go into each formula and adjust those column references. The latter formulas (March forward) use a trick to make the columns dynamic by using the COLUMN() function instead:

• =VLOOKUP(\$A8,\$A\$2:\$M\$4,COLUMN(),FALSE)

So in the case of March, it’s the 4th column to the right, and COLUMN() returns a 4, which feeds the correct number to the rest of the formula. But this example assumes that you’ll have a relatively static data set like the one above. But what if your column headers aren’t always the same? The next example shows you how to build a dynamic VLOOKUP where you don’t have to know the headers or where they’re located, you simply have the formula do it for you.

In this example you could use: =VLOOKUP(\$A5,\$A\$9:\$F\$48,2,FALSE), but you would need to manually adjust the column reference accordingly, knowing that Name is column 2, Department is column 5, Earnings column 6, and Region column 4. Or you can introduce the MATCH formula. Using MATCH, you don’t need to know the column headers, because you tell the formula to find them for you.

The formulas in B5:E5 are (the red text indicates where the formulas are different):

• =VLOOKUP(\$A5,\$A\$9:\$F\$48,MATCH(B4,\$A\$8:\$F\$8,0),FALSE)
• =VLOOKUP(\$A5,\$A\$9:\$F\$48,MATCH(C4,\$A\$8:\$F\$8,0),FALSE)
• =VLOOKUP(\$A5,\$A\$9:\$F\$48,MATCH(D4,\$A\$8:\$F\$8,0),FALSE)
• =VLOOKUP(\$A5,\$A\$9:\$F\$48,MATCH(E4,\$A\$8:\$F\$8,0),FALSE)

This is very similar to using the COLUMN() function in the previous example, except this time the columns aren’t in order, so we can’t use that. Instead we use MATCH, which finds the value we specify. In this case we match the header values in B4:E4 (Name, Department, Earnings, and Region) with the corresponding headers in A8:F8. MATCH returns a numeric match from the beginning of the data series to the end, so Name is 2, Department is 5, and so on.

MATCH syntax is as follows:

• =MATCH(Value or cell you want to find, Range to look in, 0 = Only find an exact match).

NOTE: If you leave the 0 out or put a 1, the formula will find the closest approximate match.

Next in the LOOKUP family of functions is HLOOKUP. Where VLOOKUP is a vertical lookup, looking from left to right in a range, HLOOKUP is a horizontal lookup, which goes from top down in a range.

HLOOKUP’s syntax is very similar to VLOOKUP:

• = HLOOKUP(Value you want to look up, Range you want to look in, number of rows down to return a value, TRUE/FALSE)

NOTE: Here again, 1/TRUE will return an approximate match, while 0/FALSE will return an exact match.

In this case I’m doing a dynamic HLOOKUP so that I don’t have to know the positions of the rows that I want to return.

Where the formulas in C5:E5 are:

• =HLOOKUP(\$B5,\$A\$7:\$F\$16,MATCH(C4,\$A\$7:\$A\$16,0),FALSE)
• =HLOOKUP(\$B5,\$A\$7:\$F\$16,MATCH(D4,\$A\$7:\$A\$16,0),FALSE)
• =HLOOKUP(\$B5,\$A\$7:\$F\$16,MATCH(E4,\$A\$7:\$A\$16,0),FALSE)

So for C5, I’m looking for the value in B5 (Qtr3) in A7:F16, and MATCH is returning the 4th row down for Gross Profit. Without the MATCH formula it would be:

• =HLOOKUP(\$B5,\$A\$7:\$F\$16,4,FALSE)

And you would need to manually adjust the row references as you copied the formula, so you’d need to manually determine where Gross Profit, Net Profit and Profit % fell within that range and adjust the formula to suit.

Finally, we’re moving onto what is probably the most powerful of lookup combinations, which is when you use INDEX & MATCH together. You’ve already seen how you can use MATCH to make lookups more dynamic, but what happens if you need to do a lookup to the left, when LOOKUP’s only go to the right? INDEX/MATCH is the solution, and once you get the hang of it, you’ll be surprised how easy it is.

You know that MATCH returns a reference for a value found in a table by giving you the value’s position in the table. INDEX returns a ROW & COLUMN reference in a range based on what row & column you tell it to look in, so:

• =INDEX(A1:B10,1,2)

Would return the value in cell B1, where INDEX’s syntax is:

• =INDEX(Range you want to look in, # of rows to look down from the top of the range, # of columns to look to the right of the range).

But wait, you might be thinking that’s contrary to what I said about INDEX/MATCH being able to look left! Don’t worry, they can and the following example shows how:

Note: as with LOOKUP’s you can return a matching value in the same column as the lookup value, but that’s generally pointless unless you’re just trying to see if a value actually exists in a table.

In this case, we’re using the Employee Name to return the other values in the list. Some are to the right of Employee Name and some are on the left. The various formulas are as follows:

Employee ID#:
=INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4,\$B\$12:\$B\$51,0),MATCH(\$A5,\$A\$11:\$F\$11,0))

SSN:
=INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4, \$B\$12:\$B\$51,0),MATCH(\$A6,\$A\$11:\$F\$11,0))

Region:
=INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4, \$B\$12:\$B\$51,0),MATCH(\$A7,\$A\$11:\$F\$11,0))

Department:
=INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4, \$B\$12:\$B\$51,0),MATCH(\$A8,\$A\$11:\$F\$11,0))

Earnings:
=INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4, \$B\$12:\$B\$51,0),MATCH(\$A9,\$A\$11:\$F\$11,0))

The only variation in the formulas is A5:A9, where I’m telling it what column header to find in the information table.
So the INDEX formula says Look in the range A12:F51 and first find the row number that holds the employee name in B12:B51, then find the column number that holds the header (Employee ID #, SSN, Region, etc.) in A11:F11, and return the intersection of those two values. So in this example for Sandy Stewart, for Employee ID #, I want the 4th row in the first column, which is to the left of the name column we’re looking in. So that’s how you can lookup to the left.

Now you might be thinking that’s a bit hard to fathom all at once, and frankly it can be, but there’s a trick to building complex formulas like this: you do them one step at a time. You’ll see the detail of how I got here in the sample workbook, but I’ll walk you through it just in case.

First, since I know I’ll be using Employee name as the primary driver for the rest of the formula, I start there by matching a sample name in the Name column. In this case, Sandy Stewart is row 4, which I find with a MATCH formula:

=MATCH(\$B\$4,\$B\$12:\$B\$51,0) – So find Sandy’s name in column B and give me an exact match.

Then starting with Employee ID #, which I know is column 1, I take the 4 and add it to an INDEX formula:

• =INDEX(\$A\$12:\$F\$51,4,1)

Which gives me “E033”, and that’s the fourth row in the first column.

Now I need to replace the 4 and the 1. I already have the 4, so I can add that next:

• =INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4,\$B\$12:\$B\$51,0),1)

Next I need to find the 1 for Employee ID #, which I can do with another MATCH formula:

• =MATCH(\$A5,\$A\$11:\$F\$11,0)

Finally, I replace the 1 with my new MATCH formula:

• =INDEX(\$A\$12:\$F\$51,MATCH(\$B\$4,\$B\$12:\$B\$51,0),MATCH(\$A5,\$A\$11:\$F\$11,0))

Now you can copy the final formula down, and as long as you got your Absolute references right it will automatically update for each cell it’s copied into.

Hopefully, that gives you a better understanding of the various ways to match criteria and retrieving data from both formulas, and tables.  As always, please feel free to ask any questions you’d like and don’t forget to take a look at the example workbook I put together.  You’ll find all of these examples there and you can work on all of these formulas yourself.

Smitty

Author’s note: We made an interesting discovery that is worth sharing after updating our blogsite . This article was written in Microsoft Word and pasted in The SmittyPro (WordPress 3.1) blog which caused Microsoft Internet Explorer 8 and 9 to break the blog. (The site was viewable in FireFox and Chrome.) Content would stop after the title of this entry.  After excruciating research, we found out that you cannot paste directly into the WordPress admin “Edit Post” window from a Microsoft Office product.  The legacy code and formatting from Word (or almost any other Microsoft Office product) gets pasted along with the text you have written, and this extra code causes the Microsoft IE8 and IE9 to break. This is explained in detail on WFTCyndi.com.

### 158 Responses to “Excel – IF statements & LOOKUPS”

1. Russ

Hello,

Example… I have two columns – Column A and B. Column A contains contains numbers in every row. Column B contains and “X” in some of the rows. Is there a way to tell excel to total only the numbers in column A that have an “X” in column B?

• Sorry for not getting back to you earlier, but we’ve been moving.

You can use =SUMIF(B:B,”x”,A:A)

HTH,

• Sorry for not getting back to you earlier, but we’ve been moving.

See Zack Barresse’s post on Dependent Data Validation here:

2. Aneekah

Hello
I have the following table with e.g. data:
Province| Venue1 | Venue2 | Venue3
EC | v1 | v2 | v3
WC | v4 | v5 |v6

I want a user to select a value from the Province list, and then based on that selection, the appropriate venue1, venue2, venue3 list is displayed for selection.
So in worksheet: Province | Venue
If user selects EC for Province, then a list showing v1, v2, v3 must be displayed from which he chooses one.

Possible in Excel?

3. Sherry

I have a set of data in one sheet of a workbook, and I want to transfer all the records that have an “x” in column A to a second sheet within the workbook. What would be the best way to do this?

Thanks!

• Heya Sherry,

I’d use AutoFilter and filter column A for “x”, which will leave just those records visible.

Then you can use Ctrl+G (Goto)–>Special–>Visible Cells Only, which will select just the filtered cells, then copy that range to your other sheet. It should take less than 15 seconds.

HTH,

4. I want to find the value in one column and replace it with a value in ANOTHER column. Please advise.

• You can’t use a formula to perform physical actions, but you can with VBA.

5. Cheryl Schneider

Hi!

I want the blanks in column D to automatically populate from the entries that are already there but only when both column B and column C entries are duplicate to the blank row’s B and C columns.

Thank-you!

6. JC

hi,
I want to the excel to perform a search and return a formule if it true.
example
cell A1 with value 1, A2 with value 2.
Cell B1 contain formular using if(a11=50,1,2), B2 contain formula using if(a10=20,1,2).

In cell E1, i want to search the range of column A containing value 1 and if true, use B1 formular.
Able to do that?

• I’m not quite sure what you mean?

7. Lucy Hancock

8. Jill

I have an excel with 3 worksheets of information.
I want to add a worksheet to the beginning that would reference a whole row of infomation, out of any of the 3 worksheets, if a value in the row is less than zero

• Why don’t you use a Pivot Table with Multiple Consolidation Ranges (you can invoke the Pivot Table Wizard with Alt+D+P). Then you can filter on the column with the potential 0 values.

HTH,

9. david

Hi, I have 3 spreadsheets that have sales by customer data for 3 different months. Column A has the customer and Column B has that month’s sales for each customer. Problem is it’s not always the same customers each month.

I’d like a master sheet that lists all the customers during the 3 months (without duplication) and then in columns B, C and D to show each customer’s sales during each of these 3 months.

Any thoughts or ideas GREATLY appreciated!!

• You’re much better off putting the data in one sheet. That way you can use a pivot table to summarize it.

If that’s not possible, you can use a pivot table with multiple consolidation ranges, which you can do with ALT+D+P for the Pivot Table Wizard. But you really should consolidate the data, much like you would with a transactional database.

HTH,

10. david

i can certainly put everything in one sheet. the problem is i have to run the report a month at a time. so for january i might have customers 1, 2 and 3 with revenue of \$100, \$200 and \$300. in feb i might have customers 2, 3 and 4 with revenues of \$400, \$600 and \$800.

in the interim, i put a customer master list in the first tab, and each month’s data in separate tabs and have separate vlookup formulas in columns in the main tab for each month.

• It’s not a problem to have all of the data consolidated/stored on one sheet. When you set up your Pivot Table just put Month in the filter section and you can easily switch the view to just the month you need. If you send me an example I can set it up for you.

(smittypro at gmail dot com)

11. Merrilee

I have a spreadsheet that I am using the following VLOOUP function on

=VLOOKUP(A49,area1,MATCH(\$N\$9,areapl,0),FALSE)-M49

I have set up 2 sections & named them:
area1 is for the VLOOKUP area
areapl includes 3 column header titles

I also have 2 data validations set up:
N9 gives the drop down to choose 1 of the 3 headers for areapl
M9 give the drop down with choices of SELECT YES NO

This is working fine – but – what I want to do is only have the formula that I’ve referenced above do it’s job IF the M9 cell = YES … IF it doesn’t = YES then I want the cell to be blank or = 0

Can that be done? Or am I taking crazy pills?

Thank you!

How do I do that?

• =IF(M9=”YES”,VLOOKUP(A49,area1,MATCH(\$N\$9,areapl,0),FALSE)-M49,0)

HTH,

12. david

thanks so much for putting together a pivot template for me, it is indeed very easy and i appreciate you gettimg me started! thx, david

13. brooke

I’m eyeing your index match formula. Is it possible to combine with an if statement say to return a value in a column only if it’s after a certain date?

• I don’t see why not.

=IF(A1>DATE(2013,1,1),TRUE,FALSE)

HTH,

• brooke

love it thanks!

14. Rebecca

I’m looking for some help – I’m trying to figure out what formula would be most appropriate for my dilemma. I have a sheet where I’m trying to count in a table (on a new sheet) how many times “x” appears across numerous rows for my specific “y.” Right now I’m using CountIF for “x,” but whenever I add another “y” and resort the sheet, I’m forced to readjust my formula. I thought VLOOKUP or a MATCH+INDEX could fix the issue of having to update for the new rows every time I add new data, but I’m really at a loss as how to write it. Any insight?

• Why don’t you use the entire column reference instead of a static reference?

E.G. =COUNTIF(A:A,”x”)

It would help if you post the formula that you’re using now.

15. Erno

First of all thanks, the article is great.
Secondly, the situation I have at hand is that I need to match to tables of data. Both contain two sets of identifiers: date and instance. Each date has several instances and every instance is not in each date. The value that needs to be returned is not a number I need to match the data if both criteria agree. For numeric values I would use SUMIFS. What would be the best way for other type of data:
a) is there something like VLOOKUP with multiple criteria (similar to SUMIF vs. SUMIFS)?
b) or should it be some sort of IF combination?

• I think you’d need to post an example of the data. This sounds like it could be kind of complex, so head over to the Mr. Excel forum (www.mrexcel.com/forum) and put up a new thread. You’ll have thousands of eyes on your question there.

16. Sushma

in Sheet1 if cell A1=Sushma
And in Sheet 2 If cell A1=Sushma and b2 = 25 i need the value 25 as output in sheet3 when i search for sushma.

• So wouldn’t VLOOKUP work for you?

=VLOOKUP(A1,Sheet1!A:B,2.FALSE)

Where A1 is the value in Sheet3 that you want to look up.

HTH,

17. Jan

I need the formula to return an amount from a table that intersects a column and a row. Table colum headings are 1,2,3,4,5; row headings are a,b,c,d,e; I need the amount for employee xyz equal to the amount listed in column 4, row c.

Thanks!

• Look at the INDEX/MATCH example.

E.G. =INDEX(B2:E6,3,3)

You’ll want to use MATCH to return the row (3) and column (3) results dynamically.

18. Charmaine

Smitty

I have a master sheet with 10 additional sheets where I search for data with a vlookup. Problem is I need to use a vlookup with more than one lookup value. Is this possible?

A sample is:

The lookup value is the name of the town in one column, then also the item like cards ordered in a second column, this is also a lookup value, the result should then be the number of cards ordered for this town.

• Sounds like you could use a helper column concatenating Town Name & Cards in the destination data. If you add a similar helper column in the source data, then you can VLOOKUP off of that.

19. cmallon

Can any one help me with the query below, please?
See criteria for setting the Product Groups

Product Group Criteria

BANANAS (All Sizes)
Search for Product Code beginning with BANL
SMALL GRANNY SMITH APPLES
Search for Product Code beginning with APPL, 7th & 8th digit to be GS and for them to be small, the 10th,11th and 12th digits are to be less than or equal to () 135
SMALL GOLDEN DELICIOUS APPLES
Search for Product Code beginning with APPL, 7th & 8th digit to be GD and for them to be small, the 10th,11th and 12th digits are to be less than or equal to () 135
SMALL RED APPLES
Search for Product Code beginning with APPL, 7th & 8th digit can be any of the following RE,RG,SP,RC,FU,RS,CA,TR and for them to be small, the 10th,11th and 12th digits are to be less than or equal to () 135
JONAGOLD/JUMBLE (All Sizes)
Search for Product Code beginning with APPL, 7th & 8th digit can be any of the following JO,CO,DI,GA,KA
Search for Product Code beginning with APPL, 7th & 8th digit can be any of the following PL, CP
TRAYED APPLES (IRISH)
Search for Product Code beginning with APPT, the remaining 8 digits in the code need to contain IRCO
TRAYED APPLES (NON-IRISH)
Search for Product Code beginning with APPT, the remaining 8 digits in the code does not contain IRCO

I have product codes (examples below) – is there a way I can do the categorisation via formulae on excel???

PLCHGS0110
APPLCHPL0070
APPLFRRD13KG
APPLHOJO8085
APPLITGD0070
APPLNZBR0070
APPLNZBR0135
APPLNZRG0070
APPLNZRG0110
APPLNZRG0135
APPLNZRG0165
APPLSAGS0150
APPLSAGS0165
APPTFRRG06X4
APPTIRCO12X4
BANLBEDO28LB
BANLCOFY28LB
BANLCRFY40LB
BANLHNFY28LB

• I’d think that you’d need to parse the data into the individual references that you’re looking for. From there you could use Filters or possible a Pivot Table. That should cover most of the cases, except I don’t see where small is listed in the product code, or where in the string IRCO might occur.

20. Babyfostex

can you please tell me what this if statement means? =IF(A!B39=””,””,A!B39). Your help will be appreciated.

Comment Tags: thna you

• It says that if cell B39 on Sheet A equals nothing, then display nothing, otherwise display the value in cell B39 on sheet A.

HTH

21. Babyfostex

there is verbage there on b39 but I need to edit the information but the formula is showing up not the verbage. any suggestions.

22. @Babyfostex – If just the formula is showing up then see if the cell is formatted as text.

23. Kamran

i have a list of numbers like this:
1: 2: 3: 4: 6:10
1: 2: 3: 5: 6: 8
1: 2: 3: 6: 7: 8
1: 2: 3: 6: 7: 8: 9:10
1: 2: 3: 6: 7: 8: 9:10
1: 2: 4: 6

All in one column, is there a formula that can look in very cell and see if in includes a particular number say 5?

• You could use FIND.

EG: =FIND(5,A1)

24. Kamran

@”Smitty”
i did that but it gives me funny numbers, like for,
1: 2: 3: 5: 6: 8
It gives 10
and for this
1: 2: 5: 6: 7: 10
and this
1: 2: 5: 6: 7: 8:10
it gives 7

25. Kamran

used the following if function to get the answer
=IF(ISNUMBER(FIND(5,A62)),”x”,””)

26. Find simply gives you the position of the first found instance of the search item. Kamran shows how to expand on that a bit.

27. Josh

I am working on a pro forma and need advice on an if statement. I have the monthly salary per representative. But representatives needed is a function of the number of facilities. If each rep can service 6 facilities how can i create an IF function that divides the number of facilities by 6, gives me the whole integer (since you cant hire a half worker), and multiplies this value by the cell which contains the monthly salary?

• I think you’d want to count the number of facilities by rep in case it’s not 6, then you can use something like this:

=INT(Facilities/6)*Salary

Where INT will give you the integer value of the result.

HTH,

28. Josh

@Josh
To expand a bit, I want it to determine that if the facilities value falls between 7-12, i need 2 workers * monthly salary (13-18 = 3, 19-24 = 4, etc)

29. I guess you’d need to post a bit of an example. If you go the Mr.Excel forum (www.mrexcel.com/forum), you can post examples of what you’ve got.

• Josh

Actually, the first formula you gave nailed it. Thank you!

30. Hi,

SIR I HAVE A SHEET IN WHICH I HAVE 6 COLUMNS FOR RICE INVENTORY.

1)Report No
2)IN/OUT/PROCESS/Fg(This column show whether Rice In, out,Proceeds & Finshed goods)
3)Product Name
4) In (If In & FG i have make entry of quantity in this column)
5) Out (If out or process i have make entry of quanitity in this column)
6) Balance

Now i want that balance column check the report number, product name , & whether it is IN OUT Or other so it give me balance

If it is IN or FG so it add or if it out or Process its less it.

• Have you tried SUMIF?

31. RJ

I’m creating what will become a dynamic budget in Excel. I have one worksheet where I am hardcoding all of my categories and values (i.e. Stage Manager – \$50,000, Director – \$80,000, etc.). Several of the categories have a value of \$0 (for example the General Manager category, because we don’t have a general manager currently, though this may change in the future). I want to pull, into a second worksheet, only those categories and values that have a value greater than \$0. How do I do this?

(BTW I don’t want to use a filter because this budget will be updated often, so I want to be able to change values on the first worksheet and have the second worksheet populate according to the changes in value for each category in the first worksheet).

Hope someone can help!

• Without seeing your layout is a Pivot Table a possibility?

Otherwise, put all the possible positions on the Budget sheet and filter on 0. You can always automate refreshing it if something changes on the master sheet. It’s better to have too much information and hide it, than not have information and screw up your budgets when you don’t realize that you’ve missed it.

I need a formula that will do exactly this in a multi-sheet workbook. The formula will be on the first worksheet. It will say “Look in [Cell] to find a row-column cell location. Look up that cell’s location (for example, A1) in [second worksheet]. Return Y if the formatting of that cell in the second worksheet is bold. Return N if it is not bold.” I cannot take the time to learn VBA. How do I do this using current Excel? I am on a Mac, using Excel for Mac 2011.

33. New Gig

Hi, What formula or set of formulas I should use to look for exact match in post code from one sheet to another and then if post code matches look for exact match in property no.
I have a column with post codes in one sheet and few property numbers for each post code. Second sheet contanis full data of post codes and all property noumbers for the particular post code. I want to look only for those records which match both post code and property no.

34. AMY

I have two sheets one is my data sheet one is a list of center numbers and descriptions. I need to match one colume in my data sheet to the list of centers and fill the cell with the decription. How can I do that

• You’d use VLOOKUP.

Something like this:

=VLOOKUP(A1,’Data Sheet’!A1:B500,2,False)

If you download the example workbook, you should find a suitable example that you can copy.

HTH,

35. Debs

Hi, Great site btw.
I’m trying to workout the best function/formula for the following scenario:
Worksheet 1:
Emp.ID: 0005
Start Date: 1/4/14
End Date: 7/4/14
Total Hrs: ?? best formula/function ??

Worksheet 2:
Dates: 1/4/14, 2/4/14, 3/4/14 …
Emp.ID: 005 10 12 8 … (hours)

• Sorry for the late reply, but my desktop has been down and I just got back up with a new motherboard.

Does sheet 1 only include data for 1 employee or are you listing information for multiple employees? How do total hours get split out by date if it’s not listed on sheet 1?

I’d consider using a different structure that’s more database oriented on sheet 1, then use a Pivot Table to summarize it.

36. David

Hi, I am new to excel but trying to help my wife with a more dynamic grocery list that pulls from a table.

Example: I select a Category under a DV called Perishibles, that when selected only selects products under the Items Column that have a 0 in the UnitsOf Column. Also maybe do a search and retrieval based on Expiration Date.

I have a multiple worksheet environment.
I am using a data validation cell for categories that are in a named range =Categories.

I also created a named range that includes the entire table called VLOOKUPTABLE, and individual name ranges for each column i.e. Item, Units, ExpDate.

On my program sheet I have:
(Data Validation Cell) in A2
Column 2, or B2 = Items. with a formula in B2
(=IF(A2″”,VLOOKUP(ITEM,VLOOKUPTABLE,1,FALSE))
Units of. Column 3 or C2
Exp.Date COlumb 4 or D2

First I am trying to get program sheet to just return all items under the Column Item but its only returning with the formula I came up with the first object in the list of Items under the VLOOKUPTABLE.

Any Ideas or suggestions would be greatly appreciated.
David

• Do a search on the blog for Dependent Data Validation. Zack Barresse wrote a good article on it, and I think that’s what you’re after.

37. Clueless

Hi, I have question. I have different look up value column if my column A starts with a different number. For example, I want to use the value with in cell with column header “balance”, if the value in my column A starts with “1” and if the value in A starts with “2”, I would like to Column e – column f, etc…can this be done?

Many Thanks

38. TracyP

Love this site! Thanks for the valuable and easy to follow help

I have two spreadsheets with data, with dates and amounts both in columns. I want Excel to basically try to match up 3 pieces of information and if they match, to tell me so. I’m looking first at a name, then a date, then an amount. They exist right now in 2 separate workbooks but I can put them together if you think that’s better. Anyway, I am grateful for your suggestion – trying not to build a heavy “IF” statement but not sure how to get around. Thank you!

39. Kacki

I am trying to figure out a formula where a dollar amount in one cell changes to zero if I key PAID into the cell next to it, but it stays the same if it is blank. Any suggestions?? Thanks!

40. david

Hi Smitty, so I’ve been doing pivot tables and could use your help again. I have a listing of customer purchases, with invoice by invoice detail, showing month/year of purchase, invoice number, customer name and number and dollar amount of sale, and which of several stores they bought from. I created a pivot table that shows for each store which customers bought how much each month. What I’d also like to know (without doing a bunch of outside transactions) is how many customers were active in each store each month. If I use the Count function in the pivot table, it counts the number of transactions each customer did which is not what I want.

Can I set up the pivot table to show the number of active customers each month at each store? And/or is there a getpivotdata function that will accomplish this?

I can certainly hardcode the pivot, use countif formulas and the like, but would be nice if I could just run a pivot each month and have the work done for me.

thanks!!!!!

### SmittyPro.com

Welcome to SmittyPro.com, your one-stop for solutions concerning anything from beginner to extremely advanced Microsoft Excel issues and programming. Topics covered include Excel, Access, VBA, and (every now and then) some amazing observations or "Smitticisms" that might dumbfound you and send ripples of excitement or intrigue around the world.