I'm using an Array formula to lookup a particular value then return a related column value.
What I would like to do is Sum a related range of cells.
The formula I have used to look up the single cell is
{=INDEX('[PL.xls]Parts List'!A4:S205,MATCH(A63,'[PL.xls]Parts List'!A4:A205,0),7)}
So in this case the related value in column 7 is returned.
I would like to sum the related values in say columns 5 to 11
I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:
=SUMIF(M12:M600,">0",(VLOOKUP(M1:M2,K12:M600,3,TRUE)))
but cannot seem to get it to work.
I need the array portion of a lookup formula to change based on a cell value.
On a worksheet(named "groupings")that groups students according to ability level, I have data validation lists where teachers select the criteria for the group. In cell I5 they choose the period, and in cell L5 they select which assessment to look at.
Cell B8 will either say high low or medium. Then in cell C8 I have this formula: Lookup_Occurence($B$8, ??????, 1,1,2)
Again the array will depend on what is entered in cell I5 and cell L5.
I have attached one of the worksheets the lookup_occurence will be referencing. In this exit card worksheet if cell B10 (the name of the assessment) matches what the teacher chose in cell L5 on the groupings worksheet, then the array formula will be 'Exit Card'!Card1. If cell G10 matches what the teacher chose in cell L5, then the array would be 'Exit Card'!Card2. Where A9:C339 is named Card1 and cell F9:H339 is card2 etc.....
However, it gets even more complicated then that because within Card1 there are 8 periods, and if a teacher chose period 1 in cell I8 on the groupings sheet, then I only need the array to look at the period one data in the card1 array.
Essentially what I am trying to accomplish is create a page where teachers can group students by ability level according to any assessment they choose. On the exit card page the formulas currently equaling # Div/0 will say either high, med, or low depending on how a student performs on that assessment.
Assuming 1st row is a header row
Sheet1, Column A
1230000_XL07 - WB OPS
1230001_XL08 - WB OPS
1230002_XL09 - WB OPS
Sheet 2, Column A
1230000
How do I lookup 1230000 and return 1230000_XL -07 WB OPS in B2
I want to know how to copy a range into an array then an array back into an range.
Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)
untitled 1.JPG
i want result "z" in b1 in all three condition of attachment
A b c d
100 100 x
100 y
100 z
I want to lookup 100 in colum b with a result of z instead of x in criteria c:d
I am trying to write a function that will look up values in an array, and then return a value from a separate array. where:
Column A has a name and dates:..Column B has a number associated with the date (0 or 1)
"John" ..............................................0
6/1 ..............................................0
6/2 ..............................................1
6/3... ..............................................0
6/30 ..............................................1
I am trying to make a summary table that shows the dates associated with each time "1" occurs
So it will look like:
column C: ...... Column D: ...... Column E:......Column F:
John.................6/2...............6/30............... N/A
Bill ............... 6/1...............6/2............... 6/30
Fred ...............N/A ...............N/A...............N/A
and so on:
I've tried excel formulas, but can't get the right date returned.
I have two columns of data, one containing Groups, another containing Items. Like this:
Group | Item
A | Apple
A | Orange
B | Pear
B | Banana
C | Tire
D | Coffee
etc.
On another sheet, I have each of the groups listed as column headings. I want to resort the data and display it in columns instead of in a single list.
I can write a VLOOKUP to search for "B" for example, and return "Pear", but I want to return all matches for "B" not just the first one.
The formula you see below works great. It will lookup to the closest value that matches the criteria.
=LOOKUP(2*F2-0.000000000001,C1:C4+C2:C5,D2:D5)
Here is the problem when I try to expand the range I get a zero.
=LOOKUP(2*F2-0.000000000001,C1:C19+C2:C20,D2:D20)
Is there anyway we could overcome this?
I know I could use a formula array for this, but I would like to know if this could be done in a non-array formula ....
I have attached a worksheet which I need to show the last update and last date customer contacted, The formula :
=LOOKUP(2,1/($F2=Data!$D$1:$D$10000)/(Data!$B$1:$B$10000=MAX(IF(($F3=Data!$D$1:$D$10000)*(Data!$M$1:$M$10000=COUNTIF($F$2:$F3,$F3)),Data!$B$1:$B$10000))),Data!B$1:B$10000)
is used to output the last contact and the formula contained within column Av, Both of which contain #N/A
I have 40 or so case studies on one worksheet. Each case has four columns- the first is the year, and the next three are values for that year. Each study is 140 years long, but they don't all start in the same year. What I want to do is pluck all the values from the fourth column of each study, and total them up for each year.
View 9 Replies View RelatedI have a spreadsheet that I would look up two criteria in the table array, which first is down the column and next cross the row.
It would be easier to explain the scenarios by referring to the attached file.
I have a grid like follows (how to format):
2013-08-16-5.00 0 0
2013-08-16-2.0-19,902 -20,734 -21,429
2013-08-16-1.011,431 11,907 12,297
2013-08-160.0-0 0 -11
2013-08-161.0111 153 140
2013-08-162.031 61 60
[Code]...
Basically I want to return an array (to pass to another function) containing all data that matches my search. E.g. I want to retrieve an array of the data that matches 2013-09-20. Is the only way to do this a VBA func?
I need a look-up function that can return a value from an array that can be 3 to 7 rows in height. I want the function to always look at the last row in the array.
This is how it looks:
Column A Column B
blank row
1000
A
B
C
SUM 15
blank row
In this case I want the function to return 15 if I search for 1000. The problem is that there are not always a fixed number of rows beneath the searched value.
I have 40 or so case studies on one worksheet. Each case has four columns- the first is the year, and the next three are values for that year. Each study is 140 years long, but they don't all start in the same year. What I want to do is pluck all the values from the fourth column of each study, and total them up for each year.
Here's how the sheet is set up- row 6 is a header row with four repeating column heads- year, value1, value2, value3. So year is in col A, E, I, M, Q, etc. up to FM. Value3, the one that I want totaled is in col D, H, L, P, etc up to FP. Each year column begins with a different year, (with some repeats) but each is 141 rows long, so that the first study begins in 1994 and ends in 2134, the second is from 1985-2125, the third is from 1991-2131, etc. I want excel to find all the value3's from 2007 and total them, all the value3's from 2008 and total those, up through 2145. The data range is A7:FP147. I tried adjusting some of the suggestions, but no luck. I was thinking I'd have column FQ with all the years 2007 on, and FR by the total of all the value3's from that year.
I am looking to write a macro that can look at a value (i.e. "X") in a cell and compare it to the elements in a 2D array (where one dimension corresponds to the start value and the other to the end value) that is found on another worksheet. If "X" falls between the starting and ending numbers then I would like the macro to return a string (say "Test1") to a cell on the same row and a couple of columns to the left. The string values are listed to the left of the start and end values on the first worksheet and are elements to a 1D array. I am trying to make this " loop" through all the cells that contain run numbers (I have highlighted the columns in yellow on the attached workbook). There are multiple (i.e. +12) worksheets in this project but I have only included two in the sample I am providing because of file size.
View 4 Replies View RelatedI have a table of data with years in row 1, week # in column C. The data array is from C2:O34.
I want to find the max value in each year column, then look up that result and return which week that value occured.
Is there a vlookup that allows you to specify which column of the array has the lookup value and which has the lookup result?
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
View 3 Replies View RelatedI've a question. For instance
apple,lemon,strawberry
fruit
green,blue,white,black,grey
color
...
...
...
...
I've a list like above. How can we use lookup formulas from comma-separated cells?
white> color
lemon> fruit
Am trying to figure out the best way to combine into 1 cell an ISNA(Match... and a Lookup array that will run if the ISNA(match ... returns a false value.
Right now it's running in 2 separate cells ISNA(Match ... and then an if statement that if the ISNA(Match returns false, that the look-up runs, if it doesn't return false, then the value is 0)
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?
View 3 Replies View RelatedI am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas.
One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID
V1
V2
V3
105
27
3
149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies View RelatedI know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
View 9 Replies View RelatedI have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.
There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.
Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.
View 2 Replies View Related=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx
Following on from a previous post need to add to my (forums) array.
I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.
I have a list of ID Numbers and Dates. Let's call this "List 1".
I also have a second list "List 2" which also contains a list of ID Numbers and Dates as well as a third column for Rating.
I am trying to extract the Rating for each ID Number from List 2 and display in List 1. The problem is that the Rating can change with time, so List 2 contains several different Date and Rating values for the same ID Number.
What I need to do is lookup the ID Number from List 1 and return from List 2, for that ID Number, the Rating at the closest date prior to the date for that ID Number in List 1. Example below:
List 1
List 2
ID Number
Date
Rating
111
17/04/13
5
[Code] ..........