I want to sum all expenses on Company B within Expense 1. Is it possible to use MATCH/INDEX or something else to do that? I can't use SUMIF, because I don't want to include Company B within Expense 2 for instance.
View 5 Repliesim trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
I would like to lookup and return results based on the values of two cells A1 and B1.
The returned results are from column G to column I and always returned in the same location of the spread sheet.
For example if P2560 is selected in A1 and D10 in cell B1 then after lookup the results would be the values of G3 to I4. Alternatively if A1 is P2560 and B1 is D20, then the results are the values of E5 to I7 and so on.
see the attached spreadsheet for more details. This shows the main data (A2 to I15) and the expected results depending what value is selected for cells A1 and B1.
I have data that contains all the days in a year G7:I372. I want to lookup a value based on month, date and return the result. The sheet I am using contains a list in A3. As I change the value of A3, i want the dates to update. See attached sheet.
View 5 Replies View RelatedI've been searching both here and on google but I've been unable to make use of any of the numerous suggestions and examples I've found to do this; given I'm right in thinking that this should be done with a vlookup!
I have a table in which different customers pay different prices for different products. C13 is a cell in which I want Excel to look up the correct value based on A13 and B13 (in this case C3).
I have no experience with lookup functions, which might be the reason why I can't get this to work. I bet it's pretty simple
I have a database of customers that are all sorted by a customer number.
I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc.
Here's my question;
How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date.
=IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))
I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
View 1 Replies View RelatedI'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is
=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
Sheet1ABCDEFGHIJK12ItemWidthHeightPrice
3mm6307809301080123019751821#N/A
49001581681791891992512001621741851952083615001661791912032167
18001701851972102228210017418920321623192400179193208224239102700
18520121823324911Excel 2010Worksheet FormulasCellFormulaK3=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI need a formula that will calculate the sum total of 6 and a longer formula to cope with 12 letters in a range. The third value in both ranges is worth double points.
The letters and their corresponding values are:
F=0, P=2, M=4, D=6
Some 6 letter examples to illustrate:
in A3:D3 the letters D,D,D,D,D,D return the value of 42 in E3 (6+6+12 (third letter is worth double)+6+6+6
in A4:D4 the letters P, P, F, F, M, M return the value of 12 in E4
Some 12 letter examples to illustrate:
in A3:L3 the letters D,D,D,D,D,D,D,D,D,D,D,D return the value of 78 (6+6+12+6+6+6+6+6+6+6+6+6)
If the range is empty the formula should leave the cell blank.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I want to put in place a CF icon set of either a green tick or a red cross in cells A1:A20 but when I get to "Manage Rules option box" It is only showing me that I can use "values". Is there a formula that i can use to make it choose the icon set based on text of either "y" or "n" in cells A1:A20 .
I have tried to just change the font to "wingdings2" but other need to use the worksheet and they wont know to use the corresponding text of "O" & "P" .
I have one cell (B10) that will contain either "high", "medium", or "low". Based on one of these 3 values, I'd like to shade cell J15 a color with some white text. I got 1 of the 3 keywords working, not sure how to add in the other 2.
View 2 Replies View Relatedscenario: Column H needs to be formatted to green if the text in Column F does not equal "Dog". Dog could be either DOG, Dog or dog. I know how to get it done with only dog, but this one is a bit tricky.
View 2 Replies View RelatedI have a list that changes:
A B
Apples 1
Apples 1
Bananas 1
Carrots 0
Carrots 0
Donut 1
Donut 1
Donut 1
Donut 1
Egg 1
Fish 0
I would like a to create a function in one cell that counts the unique text values in column A, but only when there is a corresponding 1 in column B. So the answer in the above example would be 4 (Apples, Bananas, Donut, Egg).
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
the example above would then become abca cabc
I am wondering if it is possible to format a cell that contains text - based on a number in the cell next to it. Here is what I have:
Category......Mean
Results.........3.3
Morale..........3.8
Buy-In..........4.0
I would like the word 'Results' to display in BLACK text because the mean score is in the range 3.0-3.9 (same goes for the word 'Morale'). I would like the word 'Buy-In' to disply in GREEN text because the mean score is in the range 4.0-5.0. Basically, the mean scores are calculated using formulas and I would like the color of the category to change based on the mean score number. Is there a way to use conditional formatting to do this? Or another way to automatically make this work? File is attached.
I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.
Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.
I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.
I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1
I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:
excel.jpg
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies View RelatedI am trying to apply a conditional format to an entire row based on the presence of any value in the first cell of that row to format to a certain color. If there isn't something present in the first cell, I don't want it to change.
Everything I keep trying ends up highlighting the entire workbook.
I would like to apply the rule to the entire sheet but only have it highlight the rows which have a value/text in the first cell of each row.
I am assuming I should use the formulas selection for conditional formatting but I'm unsure of what formula would apply to this type of request.
I have 3 Sheets named Paid, Rejected, and Reprocessed.
On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)
On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount
There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.
Any thoughts on which formula I should use?
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies View RelatedI try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
View 3 Replies View Related