Vlookup Array Formula Too Slow - Don't Want VBA
Apr 28, 2007
I have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER.
What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.
View 9 Replies
ADVERTISEMENT
Jan 29, 2010
I have the following information (small sample only):
CodeItemList
162AVS0001 AVS0001
162AVS0002 AVS0003
162AVS0003 BUD0002
162AVS0005C5O1208
102BRE234CAL0007
102BRE-508EQU0003
102BRE-AB09 FXUK21916/2
162BUD0002GMC0003
162C5ENV-TNT HSS0001
162C5O1208 HSS0006
162CAL0005 IBM0003
162CAL0007 KUN0002
The list on the right is derived from the following array formula which has been copied down the column to end of the data range:
{=VLOOKUP($G$130, INDEX(G133:I252, SMALL(IF($G$130=INDEX(G133:I252, , 1), ROW(G133:I252)-MIN(ROW(G133:I252))+1, ""), ROW(1:1)), , 1), 2, FALSE)}
$G$130 refers to a value in that cell which is user defined from a drop down list and in this instance, the value is 162. Therefore, I would expect to see all of the items that contain 162, under the list column. As you can see, it has not returned all of the values relevant to 162. It has missed AVS002, AVS005 C5 ENV and CAL0005 in this example. The ommissions seem random
View 2 Replies
View Related
May 8, 2006
my data is formed of 2 columns column 1 (currency) & column 2 (balance)
i am writing an array formula using the vlookup function, the formula enables me to add all balances after converting them to $. i dont know whats wrong with my formula
please find attached an example of ....
View 5 Replies
View Related
Jun 29, 2007
I am getting the hang of this formula array stuff but I have another issue. I have gotten this to work in the actual Spreadsheet but when I try to put that same string into Code I get an error. I am trying to add a Vlookup into an Formula Array. I am building a new sheet and adding this to the sheet to do the calculations. I was able to get the code to work fine until I added the Vlookup section. I am doing this in small sections to make sure each part works. I am getting "unable to set the FormulaArray property of the Range Class" error message.
NewSheet.Cells(CurrRow, 4).FormulaArray = "=IF(ISNA(INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3)),0,INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3))"
View 4 Replies
View Related
Nov 10, 2009
I have a function grabbing latitudes and longitudes from a zip code look up. The table array used is approximately 48k rows.
Is there a way to speed up the lookup?
View 9 Replies
View Related
Dec 24, 2007
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
View 6 Replies
View Related
Jan 3, 2008
I 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)?
View 5 Replies
View Related
Jun 7, 2014
I am currently using the following formula:
=VLOOKUP($A$3, Sheet1!A:B, 2, 0)
=VLOOKUP($A$3, Sheet1!C:D, 2, 0)
=VLOOKUP($A$3, Sheet1!E:F, 2, 0)
The problem is I am trying to get the table Array portion (A:B , C:D , E:F etc.) to auto fill when I drag it across and it will but not correctly. How to autofill with the pattern that I need?
Currently it would auto fill with D:E , F:G, H:I G:H I:J
View 1 Replies
View Related
Nov 27, 2008
I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
View 10 Replies
View Related
Dec 28, 2008
I have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead. I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.
I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns. I have attached a workbook.
View 2 Replies
View Related
Dec 11, 2007
I'm working on a project in Excel (for a restauraunt) and I basically need to make a formula to work out what an item is, using it's ID number and referencing the certain menu that it is in.
as you can see the table array is defined in cell E3, but i still get a #N/A result, and when I replace the "E3" in the formula with "Deserts" it produces a result.
View 9 Replies
View Related
Dec 5, 2008
I am using a VLOOKUP formula, but when I drag it down it doesn't keep the same array selection. Is there a way so that I can drag it and it relates to the cell to the left, but the array selected stays the same. I don't really want to type the same thig out for every cell as there are 6000. I am using: =VLOOKUP(D2,Working!A2:C23,2,FALSE) and want D2 to change accordingly but for working!A2:C23 to stay the same.
View 2 Replies
View Related
Aug 29, 2013
I am looking to be able to alter my table_array section in VLOOKUP to adjust in date.
exampe: =vlookup(A4,'[Daily report - August 25.x;sx]Facilities'!A4:AY100,84,FALSE)
and I want to be able to change the August 25 -> August 26 repeating so that as I drop the next date in it will update to the correct tab.
I have the dates above so if I could somehow just the date to another cell instead that would work as well. I just do not know how.
Essentially I need to grab data from a separate workbook everyday and compile it to one master list.
View 3 Replies
View Related
Jul 23, 2009
This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.
This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?
View 2 Replies
View Related
Mar 4, 2014
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
Here is my workbook: Book1.xlsx
View 4 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Aug 22, 2009
I have previously been performing a multi level SUMIF of named ranges with a large data range.
This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.
Basically, i'd like to select a range of rows which will parse into the SUMIF formula.
I have solved this by referencing an external cell to create the range.
View 6 Replies
View Related
Aug 23, 2007
I have the following formula in 220 cells in a workbook:
=( SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$H$1:$H$44995=$A5),'General Journal'!$I$1:$I$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$J$1:$J$44995=$A5),'General Journal'!$K$1:$K$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$L$1:$L$44995=$A5),'General Journal'!$M$1:$M$44995))
Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.
You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.
View 8 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
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
Jul 25, 2014
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
View 10 Replies
View Related
Apr 3, 2014
I have a spreadsheet with 51 worksheets - a "Master" worksheet and 50 datasheets (labelled 1-50). Within the "Master" worksheet the first column has numerical values ranging from 1-50.
One of the columns in the "Master" worksheet is a VLOOKUP that is trying to pull data, of which the table array is dependent upon the value in the first column. For example:
If cell A2 has the number 1 the VLOOKUP would be:
=VLOOKUP(E2,'1'!$A:$E,3,0)
If cell A2 had the number 2 the VLOOKUP would be:
=VLOOKUP(E2,'2'!$A:$E,3,0)
If cell A2 had the number 3 the VLOOKUP would be:
=VLOOKUP(E2,'3'!$A:$E,3,0)
etc.
Tried nesting an INDIRECT function with the VLOOKUP but it didn't work.
View 2 Replies
View Related
Apr 7, 2014
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).
View 7 Replies
View Related
Jun 11, 2009
I need to be able to do a look up in 9 different table arrays using 9 different ranges.
I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.
I am not sure how to make this smaller or work.
View 14 Replies
View Related
Apr 9, 2012
I am making a report with product usages for a given week. There is a file for each week.
At the moment I am using vlookup function and manualy change path to source file every week.
=VLOOKUP($A$1,'A:FilingDataMatrix 2012Production Matrix[Production Matrix 2012-Week15.xlsm]Mon - Ingredients'!$E:$F,2,FALSE)
Now I would like to avoid the part with manual changing. Instead I would like to have an ability to take the file path from separate cell.
View 2 Replies
View Related
Sep 13, 2013
I am trying to build a udf to replicate a vlookup where the table array is in another file.
The scenario is that very often I use store numbers and want to add the associated name quickly without having to open up the reference file and use a vlookup. So I am looking for something like =storename(number)
View 2 Replies
View Related
Jul 18, 2014
I'm trying to lookup XYZ plc in an array where the company names in col A are like XYZ plc (UK, London), ABC plc (Boston US) and I want the lookup to return a date in column 3. I've tried VLOOKUP(B2, LEN(LPR), 3, FALSE) where B2 is the cell holding XYZ plc and LPR is my array and it's not working.
View 2 Replies
View Related
Apr 8, 2009
I have one sheet that contains data about products, the product number, the class it belongs to, and many other bits. The data is sorted by product number.
In a second sheet I have a list of classes in column G, I'd like to return a list of product numbers in columns H to wherever is needed.
For example there might be two products in class one, class one is in G1, with the first product number in H1 and the second in H2.
View 9 Replies
View Related
Apr 25, 2006
I have created a workbook consisting of several blocks of similar data. To make updating the data easy I have arranged each block on a seperate sheet. IS there any way to retrieve data from this 3D array using LOOKUP functions based on data from drop down lists? I have tried labelling each block of data on it's own sheet and trying to retrieve the label from a list using VLOOKUP but each time the label is retrieved as a text string and gives an error message when used in another VLOOKUP. I.e. =VLOOKUP(A10,(VLOOKUP(A13,'0'!C21:D31,2,FALSE)),3+(IF(B10="No",3,0)))
The interior VLOOKUP is retrieving for example "a" instead of a, when the outer VLOOKUP encounters this it returns an error. Is there any way of fixing this, is it a shortcoming of Excel, is it me or will I ahve to go back to creating one big data array on a single sheet?
View 2 Replies
View Related
