# Write VLookup Where Data Array Changes Each Time VLookup Used

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
ADVERTISEMENT
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
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
Feb 22, 2010

Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.

View 3 Replies
View Related
Mar 14, 2013

how to write vba code using vlookup formula insert pic from picture folder

View 5 Replies
View Related
Sep 25, 2008

ColumnA Column B ColumnC (Yes/No)

2121 345766

23423 6456546

4234 6456456

7567 64566456

76756 654645

67567 3344534

76575 34534534

756756 45345

7657 534545

756767 4534534

756756 435345

Hello,

What I am trying to do is

Search the number written at ColumnB in ColumnA and if found write YES to column C

if not write No

View 9 Replies
View Related
Jul 13, 2009

I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.

View 4 Replies
View Related
Jul 1, 2014

I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.

I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.

What I've already achieved:

The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.

a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.

[Code]......

What I would like to do next:

The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.

E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.

The code that I am currently using for my confirm button is:

[Code].....

Attached File : Userform Contact Sheet - 27th June.xlsm

View 2 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
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
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 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
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
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
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 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
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
Jul 8, 2006

=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)

I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?

View 9 Replies
View Related
Sep 20, 2006

I am working with a friend on their spreadhseet which uses several vlookups in order to pull information cells in one worksheet to another, and also to pull object types from a list with values saved as an array. However, one item in the array consistently does not appear.

I noticed that his array has 4 columns, unsorted, where I would have used 2 (one with the object type, and the second with the value corresponding to that object type) and then sorted them.

The error he gets is "A value is not available to the formula or function", even though the value is in the array.

I have already eliminated formatting, spelling errors, bad formula (it works for the other types), and all I can come back to is the array.

The file is too large to attach, so I am attaching some description of the formulas and the structure of the array.

View 9 Replies
View Related
Jun 2, 2013

I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.

View 9 Replies
View Related
Aug 22, 2014

I need to make a vlookup which returns values for many 1000 material numbers. The values is located several other files. My problem is that i dont want to update all vlookups every time the other files (with my values) are getting updated.

e.g.

i want to have en cell A1: FileNameVersion1 and when someone updates the file the only thing i have to do is to change the name in cell A1 to FileNameVersion2.

View 3 Replies
View Related
Dec 3, 2013

find a formula that would act as a vlookup but would look up and return a value even if it is not on the first position within the array. I have an SAP export that has on Column A the batch number and on column B the item description. The thing is that within the multiple batch number arrays it allocates on column B the item description only for one row and randomly (sometimes first, last or in the middle).

View 6 Replies
View Related
Jan 20, 2014

I have a vlookup function that works for me in my workbook, when the data range and array is specific, but my data range will expand as will the array, as time goes on. I've used the "max" to produce the most recent results. Is there a way to accomplish this with an ever changing range and array? The most recent date will always be added to the bottom of column "A." I've used =VLOOKUP(MAX(A4:A25),A4:B25,2,0)

View 3 Replies
View Related
Feb 3, 2014

I have a workbook containing 2 sheets. In the first sheet I have a list of values, let's say that the list contains names:

Worksheet1

Column A

Peter

Paul

In another worksheet I have another column, also containing names:

Worksheet2

Column A

James Harrow

Paul Givens

I wish to perform a vlookup in worksheet2 but cannot use wildcards in the table array :

Vlookup ("*"&ColumnA&"*";Table1[Table or table and column];1;false). What I want should look like the following: ("*"&ColumnA&"*";"*"&Table1[Table or table and column]&"*";1;false)

In this problem, it is not appropriate to query back to front; i.e. vlookup worksheet1 instead of vlookup in worksheet 2.

I have seen an interesting post that uses macros (on this site, I think) but was not able to implement it.

View 3 Replies
View Related