# VLOOKUP Table Array Equal Cell Name (change Automatically)

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
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
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
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
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
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
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 25, 2007

Is there a way to automatically change the column index number in the VLOOKUP formula when copying the formula to columns? For example, when I copy a VLOOKUP formula from column A to column B, the cell references will change, but the column index remains the same. I'd like the column index to be increased by 1.

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
Oct 12, 2009

I am trying to work out a formula. I know it's a simple one but i can not work it out. If i work 9.5 hours or more per day I get an extra $10.50 for food allowance.

For example (see attachment) IF E17 is equal to 9.5 or higher then cell E23 should automatically read $10.50

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
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
Jun 25, 2014

i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.

I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup

View 9 Replies
View Related
Feb 13, 2014

I have a vast amount of data entries in a pivot table (over 100). I want to create a pivot table but when I click on it it says 'count' and not 'sum'. It will take me a long time to change them all, how do I do it quickly? And possibly, without the 'sum of ....' in the label of it?

View 3 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
Mar 13, 2007

= LOOKUP(L4,' Speed Charts'!A4:A73,'Speed Charts'!B4:B73)

I want to filldown this formula, but the comparison range ( sheet 2, columns A & B, rows 4-73) never changes. how can accomplish this so when i use ctrl-d, only L4 updates (to L5, L6, L7, etc..), but the range remains the same?

by default, filldown tool updates the range to A5:A74, A6:A75, A7:A76, etc

if it helps i'm using this forumla to:

1) take a value (L4)

2) find this value in the first range (somewhere in sheet 2 column A)

3) enter the corresponding value (sheet 2 column b, same row)

View 3 Replies
View Related
Dec 20, 2012

I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array

Essentially I imagine this involves the FIND function at some point.

Attached is an incredibly simplified example of what I'm looking for.

View 9 Replies
View Related
Apr 6, 2008

way for excel to automatically identify ranges given the first cell of an array.I guess the concept is simple:

Lets say given A1 is the top left corner then excel should look the offset columns for values and/or formulas,when nothing is found and a blank cell is found then that's the border column.Then same goes for rows,and when bootom row is found then the bootom right cell can be identified and array/range is found!

So how can that be done inside excel?

View 9 Replies
View Related