Converting 3x10 Array To A 1X30 Array To Run A Match Formula
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
ADVERTISEMENT
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
Feb 5, 2014
In sheet 1, I have
Col A Col B
John Sedgwy - R
In sheet 2, I have the following names
Col A Col B
Peter Walker
John Sedgwicky
When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}
gave me an error but when I did an individual match to both John and Sedgw, it works.
View 4 Replies
View Related
Jan 21, 2009
I have the following array formula that identifies the first number in the column I:
=INDEX(I1:I1200,MATCH(TRUE,ISNUMBER(I1:I1200),0))
This formula works fine however I was hoping to expand the range to the entire column. I thought (obviously incorrectly) that the following formula would work:
=INDEX(I:I,MATCH(TRUE,ISNUMBER(I:I),0))
Instead of getting the same result as the first formula, I am getting "#N/A".
View 9 Replies
View Related
Jan 26, 2010
Imagine 5 periods of sales, growing by $550 per year, from $250 to $2,250. There is a columnar table with sales in increments of $500 to $2,000 paired with margin percentages.
Using INDEX(MarginColumn,MATCH(Sales,SalesColumn,1)) successfully retrieves the correct margin % for each period where Sales is that year's sales.
So, I tried to construct an array formula in the following way:
{=SalesRow*INDEX(MarginColumn,MATCH(Sales,SalesColumn,1))}
Sales is not changing, so that only the 1st year's margin percentage is being applied to each year's sales. I confirmed this by extending the array to a multi-cell layout.
How can I correct the formula so that the margin percentage is looked up as if it were using each year's sales independently? I know I can just SUM the array once this works, but this is my current road block.
P.S. If you see this answer immediately, how might I multiply the percentages by the sales that increase by $500 and sum the whole expression into one cell?
View 9 Replies
View Related
Sep 18, 2012
complex formulas in Excel but managed to work out and create the following formula;
{=IFERROR(INDEX('owssvr(1)'!$AE:$AE,MATCH($A9&AK$1,'owssvr(1)'!$C:$C&'owssvr(1)'!$E:$E,0)),"")}
Basically I want to return a call quality score if the name and week match my criteria.
The above formula works perfectly, however, it is very slow and the sheet takes ages to update.
Is there a quicker and more dynamic way to achieve the same result?
View 9 Replies
View Related
Jun 11, 2006
I'm tryig to find a way to index data in an array that meets certain matching criteria. I am looking for an employee's rate on a given day by searching a database that lists the dates that an employee's rate was changed. I was hoping to solve it with a crafty index and match array formula but have been unable to find something that works so far. I have attached a simplified example of what I am trying to do.
View 3 Replies
View Related
Dec 2, 2013
I'm using the following formula to look at a range of cells and return the most common text entry. The formula works fine as long as there is text; if there are more blank cells than entries, then it returns a 'blank' and my formula cell is empty. How to clean this up so that it ignores blank cells?
=INDEX(C2:C11,MATCH(MODE(COUNTIF(C2:C11,C3:C11)),COUNTIF(C2:C11,C2:C11),0))
View 1 Replies
View Related
May 8, 2013
I need to find a way to find an array in a tab to use for an index/match function I have.
this is what i would normally use: =index(tab_array,match($a2, tab!$a$1:$a$1000,0),match($B$1,tab!$a$1:$zz$1,0))
This formula would usually work fine when I know that within the tab, the array in which the row match is searching doesn't change. However, that array may change in the future, to say column H, without my knowing as it is a database that gets automatically populated from an upstream system.
Is there a way to search for an array within an index/match?
View 1 Replies
View Related
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
Jul 18, 2012
I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
The formula I am currently using is:
=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))
Sheet 1 (Named: Main)
Number
Date
Formula
1
15/06/2012
[Code] ....
Sheet 2 (Named: Trees)
Number
Date
1
05/06/2012
[Code] .....
View 2 Replies
View Related
May 3, 2014
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
View 13 Replies
View Related
May 18, 2007
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
View 9 Replies
View Related
Jun 30, 2006
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
View 4 Replies
View Related
Apr 18, 2007
I have a function that will be used across 10K+ records at a time, so I was taking a look at optimizing it a bit. At the start of the routing I have a string with a 9 to 15 digit numeric value in it. I wanted to load each individual digit into a byte array. I started out using:
byVal = StrConv(sVal,vbFromUnicode)
But that actually loads the ASCII Character codes into the array not the digits themselves. From there I considered looping through the array applying CByte(Chr$(byVal(i))) but then I wondered if it would make more sense to just load the array with Mid$(sVal,i,1)... Does anyone have tested knowledge on which performs better? Is there an option C I have missed?
View 9 Replies
View Related
Sep 10, 2012
I had this code and it works like a charm, except, it crashed if the data gathered is just one element due to transpose ..
VB:
Function getAR(c1 As Range, c2 As Range, w As String) As Variant
Dim s As String
Dim arrTemp() As Variant
Dim arr() As Variant
[Code]......
View 5 Replies
View Related
Mar 23, 2008
I have a cell that contains parts that are comma separated. I want to assign each of these parts to an element in an array so I can process them using a loop function.
Example Cell A1 = t4567, g8905, z3030
partArray = (t4567, g8905, z3030)
Is there a method or function i can use to achieve this?
View 9 Replies
View Related
Aug 25, 2006
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
View 5 Replies
View Related
Oct 22, 2013
Question contained within code:
Code:
Dim MyArray(6, 1) As String
Dim i As Integer
'' Array List 1
[Code]....
'If lbxLI = any list index from Array List 1 then use the value stored in Array List 2 to give Listbox 2 its rowsource.
'For example; ListIndex "0" would produce a row source "_0" for listbox 2. Else if ListIndex "1" is selected, produce row source "_1" for listbox 2.
' I would like to achieve this from one "If" statement. I'm guessing it must be possible using a loop, I just can't think how despite a lot of messing about with code.
View 1 Replies
View Related
Dec 20, 2013
The short version:
(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))) evaluates out to series of 340 True and Falses. I need to be able to pull the nth True, and then somehow index that against AH2:341.
I'm working with a record of 53,000 entries with latitudes and longitudes and I need to cross compare them with 341 locations in the United States.
I've already done some of the heavy lifting, which supported narrow an initial list of 85,000 records to at least recognize which records where within any of the 341 points. (Less arrays makes my computer happy.)
This formula gives me the count of locations each entry is within one of the 341:
=SUM(IF(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25,1,0)) {CSE} & copied down 53k times
The actual return range is AH2:AH341 for the location number.
Now, I need to be able to list each of the 341 they are close to. The max count is 17, so I'll only need to list out 17 columns.
Maybe something like =LOOKUP(1,1/(69.1*SQRT(((Shops!$X$2:$X$341-C3)^2)+0.06*((Shops!$Y$2:$Y$341-D3)^2))<=25))),$AH$2:$AH$341)
but this only returns the first match. How do I return the 2nd, 3rd, 4th, etc?
Because of the sensitive nature of my data, we can use a much simpler array which I'll adapt; I'm mostly trying to understand the logic or formula that should be used.
Return Nth Match.xlsx
View 3 Replies
View Related
Dec 13, 2008
column A
MARY
JOHN
MARK
SUE
cell B1
zzzJOHNxx
I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)
View 9 Replies
View Related
Jan 7, 2009
If I have to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH as well as more than 1 Match?
I have 3 Rows and 4 Column Matrix
from A1 till D1 contains values as 10,20,50,40
from A2 till D2 contains values as 30,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
THen my answer would be HARRY, however if there are more such combinations present then I would require a list of all of them.
Ex2 For Multiple Lookup
from A1 till D1 contains values as 50,20,50,10
from A2 till D2 contains values as 10,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
View 4 Replies
View Related
Feb 21, 2008
What would be the best approach/funcvtion to use in excel if I had an array of variables and wanted to match them?
E.g.
1,2,3,4,5,6,7
I would like to lookup up a column and if either of these numbers are in that row to say "yes otherwise "No"
View 9 Replies
View Related
Jan 16, 2009
I matching up some ip numbers against some ip ranges and I'm finding I can only use the wildcards in the first argument and not the second array argument. Is that the case? Here's my files:
IP_List
141.188.146.000 =match(A1,IP_Range!A:A,0) Doesnt work
156.140.216.202
IP_Range
141.188.???.??? =match(A1,IP_List!A:A,0) works
I really need the first case to work (looking up an ip in the ip range). Is there a way to do this?
View 9 Replies
View Related
Jul 30, 2012
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
View 1 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Dec 20, 2013
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
View 4 Replies
View Related
Oct 2, 2008
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies
View Related
Jun 3, 2014
=INDEX('Data Dump'!$C:$C,MATCH('YTD Detailed'!B$2&A40,'Data Dump'!$G:$G&'Data Dump'!$D:$D,0))
I have to above array formula, i am looking to nest an offset within it. That looks up two columns to the right, but im having trouble.
Can an offset be added to an index?
View 8 Replies
View Related
Jan 5, 2010
I created an Index/Match array forumula in the worksheet. It works.
View 2 Replies
View Related
