Array Formula With Indexing And If Statements?
Feb 23, 2014
I am trying to create a form where my staff can use a drop down box to select their role on the drop down bar on the "Template" tab- M12 (highlighted orange) that can reference to same role on "sheet 2" and show the corresponding data in column C, highlighted in green. The output of the formula starts on "Template!23" - highlighted in yellow. I have started to use an array formula but cannot fully utilise it as I need the row reference needs to be variable. Is it possible to use multiple "ifs" to account for the changing inputs on the drop down bar? What do you think? <attached>
View 5 Replies
ADVERTISEMENT
Jun 5, 2013
I am trying to understand how to use index and match in an array formula. Probably easiest to take a look at my example sheet. For some reason, the first result is working, but the others aren't.
[URL] .....
Alternatively, here is a screenshot.
Uploaded with ImageShack.us
The formula I have tried is: =INDEX($E$3:$E$11,MATCH(1,IF($A$3:$A$11=G3,IF($B$3:$B$11=H3,
IF($C$3:$C$11=I3,IF($D$3:$D$11=MEDIAN(J3,K3,$D$3:$D$11),1,0) ,0),0),0),0))
confirmed with ctrl shift enter.
View 1 Replies
View Related
May 30, 2014
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
[Code] .....
View 2 Replies
View Related
Dec 6, 2013
I have 2 conditions that I need to apply.
View 3 Replies
View Related
Dec 4, 2008
I have a spreadsheet which I need to write a formula which looks at a field and if the number in the field starts with either 55, 50 or 78 it populates my selected field. This is an example of what I am trying to do :
View 2 Replies
View Related
Nov 22, 2008
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies
View Related
May 26, 2008
Sheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
View 4 Replies
View Related
Apr 7, 2009
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View 6 Replies
View Related
Jan 11, 2010
I am trying to add a final if statement to a formula but it wont let me and I cannot see what i am doing wrong - I wondered if there was a limit? ...
View 14 Replies
View Related
Feb 28, 2014
I am trying to tell the spreadsheet to look at each separate month and return different criteria. Current formula for single month search is:
=IF(VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F","")))))
I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F")
Then carry this forward for other months, for example, a later look at this formula would be:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO
[Coe] ...........
View 2 Replies
View Related
Oct 16, 2009
Trying to make it even more complicated....probably again misplaced my parenthesis
=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))
View 6 Replies
View Related
Jul 30, 2012
I have two cells = A1 and A2 in which there are words.
What I need the formula to do is if in A1 or A2 is "ABC" or "ABC2" , number 3 is going to appear in B1. If A1 or A2 is going to be "ABCD", number 6 is going to appear in B3.
I have this formula so far that works but cannot add any more arguments.
=IF(OR(A1="ABC",A2="ABC"),Table!F12,IF(OR(A1="ABCD",A2="ABCD"),Table!F13))
(The added numbers are found in another worksheet called "table".
View 5 Replies
View Related
Nov 27, 2012
I am trying to incorporate several IF statements into a single formula:
=IF(AB5<20,"LR",IF(AND(AB5<30,AB5>25),"C",IF(T5<>"","CLEAR",IF(AND(AB5<240,AB5>40,AA5=1),"B-T1-F10",""))))
What I am trying to achieve with this formula is:
- insert LR if AB5 is less than 20;
- insert C if AB5 is less than 30 but more than 25;
- if neither of the above is true, insert CLEAR if T5 contains something and if not insert B-T1-F10 if AB5 is between 240 and 40 and AA5 = 1.
The problem I have is that the B-T1-F10 is not being inserted where the other criteria are not met so I think I have not integrated the statements correctly?
View 1 Replies
View Related
Jul 5, 2007
I know there is a maximum of 7 nested IF statements available but I cannot get the following formula to work, even though it only has 7 IF's
=SUM(IF(Department=J$88,IF(Progress_StatusCNP,IF(Progress_StatusCSO,IF(Progress_StatusNS,IF(Current_Phase=$B91,IF(MID(Progress_Status,1,7)="On Hold",IF(MID(Group_Procurement_Involved?,1,3)="Yes",In_Year_Opportunity__2007,0),0))))/1000)))
CNP, CSO and NS are named cells, all named ranges are the same size and the formula is entered with Ctrl & Shift to make an array formula.
View 3 Replies
View Related
Mar 14, 2014
I would like to create a function where I would be able to ask and answer 20 questions. When using If statement it only allows me to use up to 7 nests.
I have created an example below with all the statements I need using IF but of course this doesn't work so I would need so how to handle this particular issue with so many questions/answers in one. Even though the delta between 5-4 and 3-2 is same I want different answers. I'm using Excel 2010.
=IF(AND(AK58=5;AL58=5);"OK";IF(AND(AK58=5;AL58=4);"1 OP";IF(AND(AK58=5;AL58=3);"2 OP";
IF(AND(AK58=5;AL58=2);"2 OP+1 NV";IF(AND(AK58=5;AL58=1);"2 OP+2 NV";IF(AND(AK58=5;AL58=0);"2 OP+2 NV+INST";
IF(AND(AK58=4;AL58=4);"OK";IF(AND(AK58=4;AL58=3);"1 OP";IF(AND(AK58=4;AL58=2);"1 OP+1
[Code]...
View 7 Replies
View Related
Sep 23, 2006
creating Named Formulas to handle nested IF & And statements, to get around excel's limit of 7 nested IF statements.
First of all, is it possible to create a Named Formula with combined IF with AND statement such as: =IF(AND(F26>=54,F26<=77.99),"2x3"," ")--I am getting a periodic error message: "Cell with block IF function should not contain anything else."?
I am trying to: 1. Contstruct two separate Mega formulas using IF & AND to determine what range of sizes , located in Col "F", should fall into which SIZE_CAT, sample of ranges are as follows:
SIZE_CATSIZE IN TOTAL INCHES
2X3 54 thru 77.99
3X5 78 thru 108
4X6 108 thru 138
2. Name these two formulas, FirstNamed and SecondNamed
FirstNamed formula is:=IF(AND(F26>=54,F26<=77.99),"2x3",IF(AND(F26>=78,F26<=108),"3x5",IF(AND($F26>=108,$F26<=138),"4x6",IF(AND($F26>=138.01,$F26<=168),"5x8",""))))
SecondNamed formula is:=IF(AND($F30>=168.01,$F30<=198),"6x9",IF(AND($F30>=198.01,$F30<=234),"8x10",IF(AND($F30>=234.01,$F30<=270),"9x12",IF(AND($F30>=270.01,$F30<=320),"10x14",IF(AND($F30>=320.01,$F30<=500),"Larger","")))))
3. Combine two named formulas with a "Master Formula" such as:
=IF(FirstNamed,FirstNamed,SecondNamed)
I got the IF with AND formulas to work in a test columns, but cannot seem to get them to respond properly when trying to get them into a Named Formula(s).
I have attached an example of the workbook.
View 3 Replies
View Related
Nov 6, 2013
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
View 5 Replies
View Related
Sep 22, 2009
find a row containing text. Like when opening a PDF file with Excel, all the pdf code is in column A. I would like to create a formula that would search column A for the text "Example" and have the row number be the result.
I've tried using index, match, find, search, etc.. and nothing seems to work.
View 5 Replies
View Related
Oct 22, 2007
I have to find the maximum value of a column (J16:J115) and refer to the name in column A corresponding with the number found... everything works perfectly with :
=INDEX(A16:A115;MATCH(LARGE(J16:J115;1);J16:J115;0))
But when there are two maximum values it only gives me the first one... is there a way I can get both (or more) names in different cells ?
View 9 Replies
View Related
Oct 8, 2008
I can drag it down to fill a series of cells within a column.
Let's say the formula in cell A1 is:
View 5 Replies
View Related
Jan 11, 2010
I am trying to use the following formula in Microsoft Excel 2007-
ROUND(IF(DataInput!$D$5 = "Black",0.81*E4*O4,(IF(DataInput!$D$5 = "Red",J4*0.025*E4*O4,(IF(DataInput!$D$5="Green",(57.00+IF(J4>25,(J4-50)*33.59,0))*O4))))),2
I want to use an IFERROR statement. If the results do not equal Black, Red or Green, I want the data to error so I can have the word FIX showing.
View 2 Replies
View Related
Jun 5, 2013
I have simple Countif and Averageif formulas that I would like to have linked to variable data in cell E6 on the Assumptions (2) tab however, the formula is not taking the cell as the criteria and only taking a hard number when I am using >=.
The example would be: I3 needs to be able to use the data changed in E6 for the >= number instead of the hardkey 29...
View 2 Replies
View Related
Jan 6, 2010
In the attached file the
„«All the data is available in the details sheet
„«Input field is B20.
„«There are 6 Rate Schemes. All the input fields are present in all
„«Again there are 3 Subline of Business
Need .. when I put a input field and select a C15 and C17 the Exposure Curve, Flexa, Other should get displayed.
Hope I made the scenario understandable.
View 2 Replies
View Related
Dec 9, 2008
I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.
indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.
=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
View 7 Replies
View Related
Jun 3, 2009
I have two worksheets that are formatted differently. One has a list of employees and the other has the same list of employees but with associated employee IDs. I need to pull the employee IDs to the other sheet.
Let's call the two worksheets A and B. On Worksheet A (the one I'm working with), here's the format (column A):
Employee Name
Doe, Jane
Doe, John
Roberts, George
In worksheet B, here's the format (column A | B | C):
Last Name | First Name | Employee ID
Doe | Jane | 1105
Doe | John | 1106
Roberts | George | 1107................
View 9 Replies
View Related
Aug 22, 2006
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View 5 Replies
View Related
Nov 11, 2009
I am trying to index and match information and trying to show the multiple values that go with it. Then I am trying to do the same with vlookup.
View 4 Replies
View Related
Nov 2, 2013
indexing complete data sets for each entered value. see example to better understand what I am trying to do. I have been trying for weeks.
Steve
INDEX.xlsx
View 14 Replies
View Related
Dec 10, 2009
I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
View 9 Replies
View Related
Jul 2, 2014
I'm trying to get a document together that will spit out an index of all the files in a folder/subfolder with various attributes like title name/ hyperlink to the file/ type/ etc. How to add a function that will create a column/pull data for the author of the files I am indexing.
Code:
Public fPath As String
Public IsSubFolder As Boolean
Public iRow As Long
Public FSO As Scripting.FileSystemObject
Public SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Public FileItem As Scripting.File
Public IsFileTypeExists As Boolean
[Code] ...........
How to get a column that lists the author of the document.
View 2 Replies
View Related
