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.
NOTE: this article is written for Excel 2010 and its Ribbon Interface. The examples you see were created in Excel 2010, but saved as Excel 97-2003. Working examples are hosted on Microsoft’s SkyDrive service for you to download.
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.
Visit My Website
This was a very good article. Is it possible to use an IF statement which uses a format (e.g. a cell color) to return the value in the cell?
Visit My Website
is it possible to color the entire row every time v lookup or index finds its match,if yes please tell me the way to deal with it
Visit My Website
Yes, just like your question on Mr. Excel, you can use Conditional Formatting. In this case you would probably use the Formula method to evaluate your criteria.
Visit My Website
What is meant by Using a fomula IF(ISNA(VLOOKUP
Visit My Website
If your VLOOKUP formula evaluates to an NA error you can use ISNA() to trap the error and correct for it.
IF(ISNA(VLOOKUP(),0,VLOOKUP())
It essentially reads like this: If the VLOOKUP returns an NA, then return 0, otherwise return the VLOOKUP.
Visit My Website
article was useful.
Still I have a problem.If you can solve this for me, I will be thankful.
I have programmed a report card generation for my primary classes.
I would like to return a blank cell if no mark is entered.I have given the sample below.
I have not entered any mark for Lakshana. But it displays D for her as grade. I want to return blank for nil mark.
Reading
1 AMISHA 5.0
2 AVAIYAMBAL 3.0
3 DEVIPRIYA.P 2.0
4 GANGASRI.B 0.0
5 KRITHIKASHREE.S 4.5
6 LAKSHANA
1 AMISHA 5.00 A*
2 AVAIYAMBAL 3.00 B
3 DEVIPRIYA.P 2.00 C
4 GANGASRI.B 0.00 D
5 KRITHIKASHREE.S 4.50 A*
6 LAKSHANA 0.00 D
Visit My Website
What formula are you using for the grades? You can use an IF statement for a qualifier:
=IF(Cell with number grade=”",”",Formula to return grade)
Visit My Website
I am seeking a formula for returning a sum of values based on criteria. I have a long list which we add to on a regular basis. I want to sum up the values in A2 which correspond with a 1 in A1 and the values in A2 which correspond with a 2. Is this something I would use an array?
A1 A2
1 $30,000
1 $940,950
1 $100,000
2 $25,000
2 $40,000
2 $103,004
3 $30,000
3 FALSE
4 $280,000
4 $518,175
Visit My Website
If the criteria is in column A, and the sum range is in column B you can use SUMIF:
=SUMIF(A1:A10,2,B1:B10)
Check your e-mail for an example.
HTH,
Visit My Website
I am trying to compare two columns to make sure they match from two different spreadsheets and then get a value in another colum from the same spreadsheet? Do i use lookup or if then statements
Visit My Website
You could use IF(ISNUMBER(MATCH(value)),VLOOKUP,”").
Where MATCH returns a numerical position of a found item in a list of values. ISNUMBER returns True/False if an item is found, which can then trigger a VLOOKUP to find a matching value in a range.
HTH,
Visit My Website
I have 6 suppliers quoting individual prices on many products. Using INDEX and MATCH I can return a list of the lowest cost suppliers and the highest cost suppliers. I have a column showing the average cost and want to return the supplier with the nearest price to the average price. Is this possible?
Visit My Website
I’d probably use either LOOKUP or VLOOKUP with the range argument set to True to find an approximate match.
Visit My Website
I have a similar problem as Amanda Bottoms discussed. I have two sets of data with three columns each. In one set of data it contains a code and start and end dates. In the other set of data it contains similar codes, most of which match but some may not. The other set also contains start and end dates. I want to substitute for the dates in the first set of data the dates from the second set corresponding to a match of the codes.
Visit My Website
It’s hard to tell based on your description, but feel free to send me a copy of your workbook and I’ll take a look. smittypro [at] gmail dot com.
Visit My Website
I have two cells that if both are in use I want to list another value.
If (A1>0 AND C1>0 then I want this value otherwise I want this value.
Visit My Website
You can do it like this:
=IF(AND(A1>0,C1>0),TRUE,FALSE)
Just substitute the values you want for True/False.
HTH,
Visit My Website
Hi
am really struggling here – I have columns of data with product descriptions in one column and prices in another, I want to set a funcion that if a particualar product is selected in one cell – I would use data validation and “pick from List” for this, it returns the appropriate value of the item in an adjacent cell
Hope you can help
Regards
Tracie
Visit My Website
VLOOKUP should do just what you want. Use a Validation list for the product descriptions and in the next column VLOOKUP to return the price.
E.G. =VLOOKUP(A1,Prices!A:B,2,FALSE)
HTH,
Visit My Website
I am trying to compare store inventories versus a master list. I want to return an X if true and a blank if false. Using an IF statement doesnt work because i’m trying to compare a number from the store list against the range in the master list. Although the statement returns true somewhere down the line, the true value gets lost in the sea of false returns so I get a blank more often than not.
Thanks in advance
Visit My Website
You could try VLOOKUP with an ISNA test. =IF(ISNA(VLOOKUP)…
Or if you’re trying to consolidate inventory counts by store you could use COUNTIF.
Visit My Website
Thanks. Right under my nose
Visit My Website
im trying to create a drop down list to a certain cell using data from other worksheet.can you help me?
Visit My Website
See my post here: http://www.smittypro.com/Blog/?p=25
Visit My Website
I have a set of test results from a training program with grades and SSN. I have a seperate list that has all names and SSN’s. I have tried V loolup, Index, match, lookup. None seem to be 100% accurate what do I do?
Visit My Website
Sorry for the delay, but your comment just came through this afternoon.
If the SSN is to the right of the name in the lookup list, then VLOOKUP should do just what you need. If it’s to the left you’ll need INDEX/MATCH.
Feel free to send me an example and I’ll see what I can do.
Visit My Website
Great article. It’s quite obvious you know your stuff…with that being said I was hoping you could point me in the right direction.
I am working on a 2 worksheet spreadsheet that on the first tab, has SKU numbers/item/description/quantity/price as line items for products that sell. The idea is that when a sales rep fills in a quantity on the first sheet, any items that are selected are auto-populated on the 2nd worksheet which will be used to have an itemized and printable receipt for customers.
Any insight you can provide would be highly appreciated.
Visit My Website
I’d use a standardized form that has rows referencing the second sheet, then just direct link to them (=A1), so when a selection is made, it’s automatically reflected on the second sheet.
To hide unused rows, you can set up a column in the second sheet that has an IF formula referencing any of the data cells
=IF(A1="","","x"), which will put an x in any cell with a value from sheet 1, then you can use DataFilter on the x column to hide non-x or blank rows.HTH,
Visit My Website
Your suggestions worked out great!
It does lead me to one more question if I may. Is there a way to make the data filter auto-populate the rows on sheet 2 when the quantities on sheet1 are entered? There are two reasons I would like to do this:
1. I prefer to not have the sales reps having to use the drop down box as they are not the most technical.
2. I want to hide the if column on sheet 2 that I created.
Again thank you so much for your fast response and invaluable insights.
Visit My Website
You can use VBA for that. You can record a macro following those steps, then either assign the code to a button, or use the Sheet_Activate event, which fires when the sheet is activated. I’d probably use a button, and add Print functionality to it, so all the rep thinks they’re doing is printing their final document.
Feel free to post back any recorded code that you get and I’ll clean it up for you, as the recorder is great, but it’s not that efficient.
Visit My Website
Hello, I use so many crazy formulas, but I’m having an issue with this one. I want to return a YES if any of the cells in a range have a number greater than 96000. I’ve tried so many things, but it’s still not right. This is where I’m at now…=IF(I2:Y2>”96000″,”YES”,”NO”). I’ve even tried making it an array {=IF(I2:Y2>”96000″,”MORE”,”")}. It seems like it’s only evaluating the first cell I2. Any thoughts? Thanks, Ron M.
Visit My Website
You can use COUNTIF:
=IF(COUNTIF(I2:Y2,”>96000″)>0,”Yes”,”No”)
HTH,
Visit My Website
Thank you so much, you seem very wise. It’s probably something I’m doing, but I get all NO responses when I apply this =IF(COUNTIF(I2:Y2,”>96000″)>0,”YES”,”NO”) and there are values in that range that are indeed greater than 96000. So I should get some YES responses too.
Visit My Website
so when i replaced the “>96000″ value with one of the acutal values “96417″ in the formula, i got a YES response. So i think my issue might have something to do with the “>” value in the COUNTIF criteria? =IF(COUNTIF(I11:Y11,”96417″)>0,”YES”,”NO”)
Visit My Website
The “>96000″ should work, and I can’t get it to work. I’m sad.
Visit My Website
This works fine for me:
=IF(COUNTIF(I11:Y11,”>96000″)>0,”Yes”,”No”)
Visit My Website
I tried it in a new sheet and it worked. The range i’m evaluating are cells with formulas that are pulling values from another sheet. do you think that this formula can’t evaluate the “result” of another formula?
Visit My Website
That really shouldn’t matter. It could be that the results aren’t being evaluated as values but text, in which case you can use — to coerce them. E.G. =–Sheeet1!A1
Visit My Website
well it must be able to evaluate the “result” of a formula. check out where i got a YES response, based on the criteria i entered. but that was entering that “exact” value in that cell as the criteria.
=IF(COUNTIF(I2:Y2,”9921225″)>0,”YES”,”NO”)
code1 code2 >96000
36430 NO
36430 NO
96413 96367 NO
36430 NO
36592 9921225 YES
90733 90749 NO
96365 NO
96360 NO
36591 NO
96413 96417 NO
99214 NO
96365 NO
96360 NO
51700 NO
96413 NO
36591 NO
Visit My Website
i’ll leave you alone now. sorry. thank you so much for trying to help.
Visit My Website
In this scenario you’d just use =IF(I2>9921225,”Yes”,”No”)
Visit My Website
If I have a list (list A) of numbers, how can I generate another list (List B) that is derived from list A but with certain criteria as follows:
Assume List A range is from 2 to 30. I want to generate List B which looks up list A but generates all the numbers less than 20 that exists in list A?
Visit My Website
Take a look/search for Zack Barresse’s recent blog here on Dependent Data Validation. It might be what you’re after.
Visit My Website
Hi there, thanks for an informative topic. I was wondering, (I am new to all this spreadsheet formulas), is it possible to have a formula that does the following?
IF(A1>=A2, A2, A1)
In otherwords, I want to compare 2 cells, and return the value of the lesser cell.
Thanks,
Visit My Website
That formula will work. Does it do what you want?
Visit My Website
I am currently using two sets of data pulled from two different queries. I then need to merge all this data into one spreadsheet in order to create a pivot table. The problem I am having is that my one query displays my project names slightly different then my other query – so when I merge the data, and then create a pivot table it won’t work because the names of all the projects are slightly different. Would one of the formulas you mentioned fix this for me?
Visit My Website
I don’t know that referential formulas would help you create consistency between the data sets. Can you give an example of the data and how it’s different?
You might also want to take a look at http://www.powerpivot.com, which is a free add-in from Microsoft that lets you build PT’s with asynchronous (disparate) data sets, provided there’s a common factor between them (like Store #, Part #, etc).
Visit My Website
Is there an IF Statement that will return a sum of cells with similar values either within the same worksheet or worksheets within the same workbook that will find these values and sum the $ amount they have attached to them? For example: If the following values Green (cell A1), Red (cell A2), and Blue(cell A3) have dollar amounts of $100 (B1), $200 (B2), and $300 (B3) the formula would find these cells and any other cells with the same color values and sum those attached amounts and return the total of in another cell.
Visit My Website
There’s no native functionality to to Sum/Count by color, but you can check out Chip Pearson’s article here that has some VBA code to do it: http://www.cpearson.com/Excel/colors.aspx
Although, for trying to sum between multiple worksheets you might want to look at using a Pivot Table with multiple consolidation ranges. I’ll dig up an MSKB link to that if you’re interested.
Visit My Website
I am trying to count values in a column of cells that correlate to another column of cells having a particular value. Can you tell me how to run a countif of the column I need the values tallied for when it is also based on a particular value in another column of cells is a “4″ or “5″?
Visit My Website
Unless you’re trying to count a condition in the first column in addition to the 4′s & 5′s, why not just do a countif on the column with the 4′s & 5′s?
=COUNTIF(B1:B10,4)+COUNTIF(B1:B10,5)
Visit My Website
Great article, thank you! Can you please advise if VLOOKUP can work across multiple worksheets? I have a spreadsheet with about a dozen tabs. I’m trying to populate a summary type tab with info from each. For example on the summary worksheet, there is Invoice #12345 which may be contained in any one of the other 11 worksheets. Is there a way I can have a cell on the summary worksheet search for Invoice #12345 on all 11 worksheets and return info whenever it finds it? Invoice #12345 will only be listed on one worksheet although it may be listed more than once and I want all listed entries returned. Thank you so much for your time (and patience!)
Visit My Website
Have you thought about a Pivot Table with multiple consolidation ranges?
Visit My Website
Hi Smitty. I am designing a program that will help our company keep track of YTD dealer sales from various manufacturers. I understand VLOOKUP functions and IF/Then statements fairly well and are using them abundantly in my program. The issue I am having is that the manufacturers don’t seem to be consistent on the individual dealer names. For instance, “Audio Specialists” may come in a report as “Audio Spec” from one manufacturer and “Audio Specialists” from another. Is there a way to VLOOKUP information from a referenced cell with a partial match?
Visit My Website
See this link for Fuzzy Matching:Reply – Quote
Visit My Website
Hi – can you tell me how I can do an IF statement to enter the value Male or Femal, if a cell shows M or F?
Many thanks
Visit My Website
You can use something like this:
=IF(A1=”M”,”MALE”,”FEMALE”)
Visit My Website
Thank you very much for writing this artical–it is extremely helpful. I had a problem trying to use 3 match criteria separated by commas. I got the dreaded “#ref!” and a flash screen telling me I had too many articale. Is there a way to do this? hopeful.
Visit My Website
Can you post the formula you tried along with a description of the scenario in which you’re trying to apply it?
Visit My Website
Is there a way to pull multiple responses using vlookup? I am creating a repair/service ticket for one of our products. I have repair codes that are linked to multiple components, which I have cross referenced in another worksheet. If the operator selects a certain repair code in column A, I am trying to build the worksheet to automatically leave the adjacent cell in column B blank (because I have other columns tied to that as I go across the row) but then fill in lower cells in column B with the appropriate components (anywhere from 1 to 4 different components).
Col A Col B
“Repair Code 1″ -Blank cell-
“Spare Part 1″
“Spare Part 2″
“Spare Part 3″
“Repair Code 2″ -Blank cell-
“Spare Part 4″
“Spare Part 5″ etc.
Visit My Website
See this link:
http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx
Visit My Website
Hi,
I’ve a spredsheet with some columns, what I what it’s to find a specific name in column B like the type of client, than another name in column E, like the specific product and amount of value in column F. This result it´s to post into another spreasheet. Tks JFigas
Visit My Website
Take a look at the INDEX/MATCH example in the article. It should do exactly what you need.
Visit My Website
Hi Smitty,
Tks for your answer and I’m doing step by step but franly it’s hard to understand such complex formulas for me.
I’ve a Spreasheet A that will be feed by a spreasheet B, so in A i must put a formula that will shearch in B for the “Name of the client”, than must search and find for the “Name of the product in another column and then must find the correct value in another column, this value must appear in spreasheet A. I’ve made the first part of the formula =Index(Range to look for; match(“Name of the client”;range) that gave me in spreadsheet A the name of the client, but i’m stucked, can you help me ?
Tks
JFigas@”Smitty” -
Visit My Website
@JFigas – You can send me a copy of the workbook and I’ll take a look.
Visit My Website
Hi Smitty,
I’ve sended you a mail but notuhing. Perhaps you didn’t receive it ?
Regards,
JFigas@”Smitty” -
Visit My Website
Hi Smitty,
I’ve tried something like =Index(d2:d82;Match(1;(c2=a82:a164);Match(ab6=c82:c164;0))) but the result was #value.
My intention was to search for a value in d with the match in name in column a and a match in name of column c.
perhaps i’m mixing numbers with text and the formulas don’t give the right results or ….
tks
Visit My Website
Hi Smitty…i have been trying to figure out how to make a commission spreadsheet. our commission is based on a few things. our weekly sales number and the markup. for instance…if you are doing under 100k a wk you are in this grid…100-150 and you are in another grid…150+ and you are in the highest grid. then each grid has a column for markup percentages…so if your Markup is <35%…you get paid at 5% commission, 36-39% is 7%, 40-43 is 9%…etc. how would i do something like that
Visit My Website
You can use Index/Match.
Markup 0 100 150
0% 4% 5% 6%
36% 6% 7% 8%
39% 8% 9% 10%
43% 10% 11% 12%
Weekly Revenue $75.00
Markup 30%
Commission 4%
Total $3.00
Where the formula for Commission is: =INDEX(B2:D5,MATCH(B9,A2:A5,1),MATCH(B8,B1:D1,1))
If you e-mail me I’ll send you a copy.
Smitty
Visit My Website
Hi!!
I have this scenario:
ID Activity Parent Start_Date Child Level
1 A1 – 2-Jun-2012 1 0
1.1 A11 A1 4-Jun-2012 1 1
1.1.1 A111 A11 7-Jun-2012 0 2
1.1.2 A112 A11 4-Jun-2012 0 2
1.2 A12 A1 2-Jun-2012 0 2
In this scenario A11 is repeating twice and I want to use vlookup function to find out the minimum of the two dates available. Child column is specify if the Activity has a child activity or not (1 means YES and 0 means NO) and Level designates hierarchy in the tree (0 means ROOT, 1 means CHILD node and 2 means LEAF node)
Visit My Website
That would probably be a good job for a Pivot Table. Otherwise you’ll probably need an array formula.
Visit My Website
@”Smitty” – I don’t want to go into Pivot Tables, can u help me out with array formulas because its getting really confusing. If I can send u the attachment of the actual excel file through mail or anything, please let me know. I really need to resolve this thing.
Visit My Website
You can e-mail me a copy of your workbook and I’ll try to take a look.
Visit My Website
I’ve Mailed u the workbook, please help me out
Visit My Website
In one spreadsheet I have SKU, Vendor, Open Qty, ex:
SKU Vendor Open
123 DEF 10
456 ABC 20
In another I have SKU, Vendor, Inspection Code, ex:
SKU Vendor Insp Code
123 ABC 1A
123 DEF 2B
123 GHI 3C
456 ABC 2B
456 JKL 5E
I am trying to bring the Inspection Code into the first spreadsheet. I am having trouble getting the correct code due to multiple vendors being listed. How do ensure I pull in the correct code for both the SKU and Vendor?
Thanks for the help.
Visit My Website
You can use a helper column to create unique references. Insert a new column before the SKU in each sheet. Then use = B2&C2.
You can now use =VLOOKUP(A2,Sheet2!A2:D10,4,FALSE)
HTH
Visit My Website
@”Smitty” –
That works – Thanks!
Visit My Website
there is a problem in excel that if i give a no. in cell who cant ant 11 digit no. but when we multiple in other cell with 100 the result is not showing properly
Visit My Website
Hi, great info but unfortunately I couldn’t answer my quesiton.
I’m trying to have true returned in column 3 if there is anything in column 1 or column 2 otherwise return false. By anything I mean number, letter or any character.
ie
Tag1 Tag2 T/F
1 2 T
1 T
h T
1/6 T
F
1 3 T
this is as far as i could get
=IF((ISBLANK(A1))*AND(ISBLANK(A2))*FALSE,TRUE)
excel added in the * and it still doesn’t work? any tips anyone?
Thanks
Visit My Website
Try:
=IF(COUNTA(A2:B2)>=1,TRUE,FALSE)
HTH,
Visit My Website
Hi,
I want to use if formula in a cell by comapiring 4 different cells.
eg. if cells A1, B1, C1 and D1 are not matching with each other cell containing formula should be updated as 1 otherwise blank.
Is there a way to do this?
Visit My Website
If the comparison isn’t like to chage, then I suppose you could nest 4 IF(AND statements. Do the 4 cells contain text or numeric values?
Visit My Website
Hi Smitty,
Yes cells contains alphanumeric values.
Actually I have 6 criterias for single cell by compairing 4 different cells (A1, B1, C1 and D1).
1. If A1=B1, cell value should be “1″ otherwise blank.
2. If B1=C1=D1, cell value should be “2″ otherwise blank.
3. If A1, B1, C1 and D1 all have different values, cell value should be “3″ otherwise blank.
4. If C1=D1, cell value should be “3″ otherwise blank.
5. If B1=C1, cell value should be “3″ otherwise blank.
6. If B1=D1, cell value should be “3″ otherwise blank.
I want all those criterias to be fixed in a single formula. Is it possible?
If possible, please provied me that formula.
Thanks in advance.
Visit My Website
Sounds like you’ll need a bunch of IF statements, which I generally try not to encourage because of the difficulty in maintaining them if things change. I’d start by building each condition, then adding them to one master formula.
Visit My Website
I hope i can explain this alright.
So i want to set up a conditional format on a cell. The cell im using has a text drop down list in it. Eg BA290, BKS, BKM (plus about 20 more).
When this is selected, inputs can be made into a table containing limits (the limits can be exceeded but shown through conditional formatting)
I want to now add two of the imputs form the table together (cells K10 and P10) to have a seperate range (1.3 -> 1.6) BUT i only want this to work for BKM selection. So i assume i have to do an IF statement to look for BKM and the range and if out of this range i want cell I1 to be highlighted (where the text list is located).
Is this possible?
Visit My Website
WHy not conditional format on the BKM condition? =LEFT(A1,3)=”BKM”
Visit My Website
Is there a way that I can use one of the above formulas to say that if a certain range of cells reads a particular name, then count the cells to the right in a different column? I hope that makes sense. I’m trying to do this for work.
Visit My Website
Maybe something like this:
=IF(A1=”Test”,Count(B1:D1),”")
Or take a look at COUNTIF & SUMIF, which lets you count/sum by condition.
=COUNTIF(A1:A100,”TEST”)
Visit My Website
Hello
I am after some advice with regard to VLOOKUP and IF Statements.
Issue one I have is, I am working off of two spreadsheets. Spreadsheet A being the master where all information is loaded and Spreadsheet B is where other people can view the data I have loaded.
I would like to have people update data in spreadsheet B and have this upload back into Spreadsheet A. However, I need to be able to move rows around in spreadsheet B and when I do this it messes with the data loaded back into Spreadsheet A. I think I need to use VLOOKUP, but cannot workout how to make this formula work from muliple spreadsheets. Can you advise?
The other thing I would like to do is auto move rows in Spreadsheet B. When I student status updates to graduated or withdrawn, I would like that row to move to a table sitting below the current student listing. I know I need an IF Statement, but I cannot work out how to write it so that a whole row will move within the spreadsheet.
It would be appreciated if you can get back to me with regard to if these are even possible, before I go crazy and waste even more time on this project!!!
Thank you heaps in advance.
Regards
Tanya
Visit My Website
Can you put in a column for status? That way you can simply use a Pivot Table to show the data based on student status.
Otherwise you’ll probably want a Worksheet_Change event to move the rows once status has changed, as formulas can’t physically alter worksheet structure (meaning that a formula can’t cut/paste data).