# VLOOKUP With INDIRECT (become Dynamic As The Table Array Part Of The Vlookup Will Change)

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
ADVERTISEMENT
Jul 27, 2007

I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example:

I want to look up the string "Example1" and the lookup range is

Example1 - monthly

Example2 - weekly

Example3 - weekly

Example4 - monthly

So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)

View 6 Replies
View Related
Jan 17, 2014

What I want is that I have a table like below (but it's long for 52 weeks) and long down with Vlookups. I want the formula with which I can just do the copy-paste and it will work. W1, W2.... are the sheet names with exactly the same formats inside.

A

B

C

D

E

5

W1

W2

W3

W4

6

Sales

10

#N/D!

[Code] .......

The base formula (for W1) is:

=Vlookup($a6;'W1'!$A:$B;2;0)

What I want, is the formula which instead of "W1" will write the sheet name which is in a row 5 (basically - cell name which is equal the sheet name), so with just dragging and moving the formula I will got the data from different sheets.

I tried this: =Vlookup(A6;'indirect("c5";1)'!$A:$B;2;0)

But I got #N/D! as in the example, instead of the numbers (yes, I put numbers into W1 and W2 sheets .

View 4 Replies
View Related
Sep 17, 2008

I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

View 14 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
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
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 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
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
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
May 28, 2009

I'm using a vlookup formula, and the thing I'm searching by is a part number. The problem is that in my table array, all the cells for part numbers have that green error flap in every cell, saying "The number in this cell is formatted as text or preceded by an apostrophe."

Some part numbers have letters on the end of them, ie: 12345N. When I go into a particular cell's error drop down menu and choose "Convert to Number," the vlookup will work with this cell. My problem is that the table array is several thousand rows, and I don't know how to fix them all at once.

View 3 Replies
View Related
Jun 17, 2009

I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.

For example, I need to pull the value for campaign 'notset' on 6/16/09.

I tried the following, but it would only work on the first and last campaign: ...

View 9 Replies
View Related
May 27, 2009

Sheet 1 contains:

Item Sheet 2

ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data

ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

View 3 Replies
View Related
Apr 3, 2012

I use VLOOKUP a lot and the simple VLOOKUP I am attempting is a very common exercise for me. That doesn't make me an expert just very familiar with it.

I have a worksheet where column A is a 5 digit reference and I use column A as the lookup value. In a worksheet in another spreadsheet I have a load of data but column A is also a 5 digit reference. I am trying to pull cell content from a single column of data from the second worksheet into the first worksheet when there is a match for the 5 digit reference.

The problem is, using the wizard, the lookup value is entered ok but when I come to highlight the range of cells in the second worksheet nothing appears in the table array line in the wizard.

References in both worksheets are numerical and sorted in numerical ascending order. Both spreadsheets have been saved.

However if I copy the worksheet from the second spreadsheet and simply copy it into a new worksheet in the first and then try referencing it as a table array it works just fine.

For such a simple exercise I always use the wizard as it is so much quicker, but I can't figure out why it won't work the way it usually does.

View 5 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
Jun 13, 2006

I am doing a postage calculator to calculate postal rates for posting.

However, I am facing some problems. In the Main Sheet, Postal rates, I need to do something

1) We are divided into Zones - 1 to 4

Can I do a Variable that if I type Zone 1 and it will Vlookup on Zone 1 sheet and if I type Zone 4, it will Vlookup on Zone 4 sheet

2nd problem is that:

The postage needed: When I did a Vlookup, when I key in 55, it will select the wrong price table, if you take a look at the attached spreadsheet, 55g for Zone 4 it came out $2.05 but it should be the following rate which is $2.40.

View 4 Replies
View Related
Jul 27, 2006

I have a spreadsheet where there are multiple records for each person. Each subsequent record contains information about one year prior to the one above it. So the first records contains info for 2005, the next 2004 and so on. I have the following formula in a cell in sheet 1: VLOOKUP($A2,'Sheet1 (2)'!$A$1:'Sheet1 (2)'!$DD$8796,11,FALSE)

I want to copy the VLOOKUP formula to the cell next to my original cell and have it begin searching on the first row after the first record and continue for each cell to the right. For example, if the lookup in my first cell (say A1) find the result on row 20, I want the lookup formula in B1 to start with row 21. So the change on the formula above would have to change the $A$1 above to $a$row below the first row found. I have a formula which tells me about the first instance of the vlookup finding the first row with the unique identifier.

View 4 Replies
View Related
Oct 7, 2008

I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.

A1 production line_id

B1 poduction_order_number

A2 L1

A3 L2

A4 L1

B2 505212

B3 504234

B4 505663

I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!

One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.

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
May 5, 2008

I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.

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
Feb 23, 2012

I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.

I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?

Code:

=VLOOKUP(A2,'C:&E1&[Myfile.xls]Sheet2'!$A:$B,2,0)

View 4 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
Oct 26, 2009

I have the following vlookup formula in my spreadsheet.

IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))

It works perfectly for my current requirement, but it looks at data in a pivot table which is created from a dataset. If I name the columns in the pivot table so instead of looking at column 67 it looks at a name range will this name range change dynamically when the pivot table is refreshed? If not how can I get it to.

View 2 Replies
View Related
May 8, 2014

I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;

Formula:

[Code] .....

However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).

View 11 Replies
View Related
May 21, 2009

I just learned how to do an Indirect.

So that i can make many pages without having to go through and change everything.

I want have excel search for a name contained in A1 in a table (so i use vlookup).

then but i want be able to change the sheet name easily.

so what i need is something like this:

=VLookup(A1,(INDIRECT("'" & B2 & "'!")),$b$6:$S$23,2,)

this does not work.

But i want the sheet name to be the thing INDIRECT looks up, while the name in A1 is the thing that the VLookup is trying to find.

View 9 Replies
View Related
May 11, 2014

I have two workbook, test1 (master file) and the other one is test_10_05_2014 with 100 some of them being updated daily. The last portion of the file "test_10_05_2014" is the date and we have different file on daily basis.

using Indirect function in vlookup should give corresponding value for the particular name. Formula used is giving error,

[VLOOKUP(A2,Indirect(" ' "&E1&" ' !$A$2:$X$1000),6,FALSE)]

View 2 Replies
View Related
Jun 21, 2007

IF(ISNA(VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))

It is the part regarding til indirect that gives me problems

This form I can get it to work

IF(ISNA(VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))

But here I need the Omklist.xls to be open. And that I dont want.

The what i want is that the user can use the sheet with out oping the data sheet..

View 9 Replies
View Related