# Using INDIRECT Function For VLOOKUP?

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).

## Using Indirect Function In Vlookup Should Give Corresponding Value For The Particular Name?

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)]

## Excel 2007 :: VLookup Formula With Indirect Function

Mar 19, 2013

Code:
=D5-VLOOKUP(\$C5,\$C\$45:\$F\$80,2,0)

I have formulas such as above in my Worksheet. So the above is in Cell H5

For the Range \$C\$45:\$F\$80, I am inserting rows (So moving down the data) and copying data from \$C\$5:\$F\$40 as values into the new space in \$C\$45:\$F\$80

I am doing the above using a macro, but when I run it I want this

Code:
=D5-VLOOKUP(\$C5,\$C\$45:\$F\$80,2,0)
to stay static, but instead I end up with

[CODE=D5-VLOOKUP(\$C5,\$C\$85:\$F\$120,2,0)[/CODE]

So my Table Array \$C\$45:\$F\$80 changes to \$C\$85:\$F\$120

How can I keep it as \$C\$45:\$F\$80

The other references in the formula seem to stay as I want them.

## 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:
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:
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.

## Using Vlookup & Indirect To Ref List And Vlookup Files

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:

## Embed An INDIRECT() Into A VLookup()

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.

## Vlookup In A Closed Sheet, By Using Indirect

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..

## VLOOKUP - Indirect RangeName Reference

Mar 12, 2008

I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).

Cell A1 contains a fuel cost.

Cell A2 contains a formula that returns a RangeName.

In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP(\$A\$1,\$A\$2,2,FALSE). The result is #NA!.

If I use =VLOOKUP(\$A\$1,INDIRECT(\$A\$2),2,FALSE), I get #REF!.

Among other things, I've also tried =VLOOKUP(\$A\$1,TEXT(\$A\$2,"@"),2,FALSE). No cigar.

## Dependent Values With HLookup / VLookup / Indirect

Nov 23, 2012

I want to extract/generate data from a database (same as like we can do with filter or pivot method) with H/VLookup or any other method.

Like:
Sheet 1 (Database)- 23-column & 690-Rows having record of 9 Different Groups)

Sheet 2 (Report)- A2 contains List of 9 Different Groups, If I select Group 3 from that list then I want to show all relevant data of Group 3(23-Column) in this sheet.

## INDIRECT With MIN Function

Jan 25, 2008

I just need to find the MIN of P6 to Px. Where x is a number that is from another sheet. (Lets say B3)

I thought this would be it, but its still not working, gives me #REF! error

=MIN(INDIRECT("P6:"& '[filename.xls]Sheet 1'!\$B\$3))

## Dependent Validation List Errors With Indirect & Vlookup

Dec 31, 2008

troubleshooting dependent validation list formula. I found the Ozgrid instructions for the Dependent Validation Lists to be verfy informative and I downloaded the "MatchingLists.xls" to use as a starting point.

My challenge is this: The MatchingLists.xls example does not take into account that there may be MULTIPLE dependent lists that have spaces, numbers and other unique characters in the list's source celles. Therefore, I have decided to reference a NameLookup (named range) that has an abbrevieated name for each each item (without numbers & spaces) I am nesting VLOOKUP within the INDIRECT formula to achieve the results I need - but for some reason it fails occasionally and I do not know how to troubleshoot!

Data Validation Source =INDIRECT(VLOOKUP(SUBSTITUTE(E5," ","_"),NameLookup,2,0))

Excel's Data Validation simply returns the message "The source currently evaluates to an error". This is frustrating becuase it is not consistent. It works with some lists and not with others. How do I troubleshoot???

My worksheet is attached for your reference. The source data for the dependent lists is on the Lists sheet. The dependent drop-downs are in columns E-H on the drop-down sheet. The formula that I am having trouble with is in "Subcategory" (column F). For some reason, I cannot choose category "26_Heat_Exchangers" to populate the subcategories. There are a few other categories that this formula breaks on and I don't know why. Please tell me this is not some crazy limitation with Named Ranges.

## Using Indirect Function With Comboboxes

Apr 22, 2011

I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?

I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).

Attached Files

File Type: Corp MASTER (3).xlsxâ€Ž
File Type: Quote form (2).xlsxâ€Ž

## Getting Data Using Indirect Function?

Jan 18, 2014

I have some data on a sheet and I want it on other sheet without using lookup function. Only "Indirect function need to be used". find attached the excel sheet.

## How To Use Indirect Function Within Sumproduct

Dec 29, 2009

I have the following formula, which works; however I need to make it dynamic.

## INDIRECT FUNCTION With VARIABLES

Feb 14, 2007

I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....

I have this formula: but it does not work

I'm not sure how to include the VLOOKUP function in my argument to

## Indirect Function Syntax

Feb 15, 2007

Always have problems getting my head round the syntax of the indirect function and am unable to find anything similar that's been asked.

I want to perform an operation on two numbers where the user selects which to use (add, subtract, multiply or divide) entered into another cell like this:

******** ******************** ************************************************************************>Microsoft Excel - 200701 - LCC.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC3C5=
ABCD14+5*2****3Normal*9*4****5Indirect*#REF!*Sheet3*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

## Indirect Function #REF! Error

Mar 1, 2009

In Excel 2007, the following cell Q14 CSE formula accurately returns the row number of the first negative value in the column P array P14:P102.

{=MAX(ROW(INDIRECT(ROW()&":"&MIN(IF(P14:P102="",ROW(P14:P102)))))*(INDIRECT("P14:P"&MIN(IF(P14:P102="",ROW(P14:P102))))

## #ref Error With Indirect Function

Jun 24, 2006

I have been searching through your forum but I can't seem to find the solution to my problem. I have two sheets: On one sheet in cell b2 I have a validation list whose source is =Indirect(Subgroup) which gives a #ref error. However when I evaluate Subgroup, I get a legitimate range. I have attached an example of what I am trying to do.

## Using INDIRECT Function With Double Quotes?

Aug 1, 2013

how to use the indirect function to pull data from a pivot table. This is the formula I am trying to recreate: =GETPIVOTDATA("sum of BOE",\$A\$14,"CLASS","PROVED","Years",2013)

I can't seem to get the indirect function to work properly with the words in double quotes, such as "PROVED". How do I format that part of the formula properly?

This is as far as I can get. =GETPIVOTDATA("sum of BOE",INDIRECT(\$M13),"CLASS","PROVED","Years",\$N13) \$M13 refers to \$A\$14 and \$N13 refers to 2013

## Using INDIRECT Function In Multiple Rows?

May 16, 2014

I am building an attendance spreadsheet and trying to copy and paste all of the names from Sheet 1 to Sheet 6 for a different purpose. Thing is I need the names to match so I'm not having to copy and paste every time my data changes.

My problem was that when I insert a row into sheet 1, sheet 6 accommodates and then I'm missing data and have to insert a new line manually.

At first I tried using absolutes (\$) to fix the problem, but that's a different ballgame.
I've discovered INDIRECT and so now use this formula: =INDIRECT("'DIRECTORY 2014'!B5"). It works.

But I have almost 300 entries. Is there a way to expedite the process without having to change each entry? I don't want to have to retype the function on every cell.

## Can't Get Indirect Function To Work In Menu

Dec 27, 2013

I have a series of menus in column C. I need column D to be able to access them and return the proper data.

I am using =INDIRECT(SUBSTITUTE(10C," ","")) and keep getting error after error.

MainstreamProductMgr2013-1.xlsx

## Using Indirect Function To Sum Across Multiple Sheets

Jan 19, 2009

My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:

=SUM('Jan-04-08:Jan-16-08'!A1)

But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:

=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))

where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.

## Data Validation - Indirect Function

Jun 9, 2009

i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams

but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?

eg - 1st drop down box - Football, Rugby, Cricket

2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players

## Hiding !REF# Error Using =indirect() Function

Jul 7, 2009

I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.

I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.

I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.

## Trailing Covariance With INDIRECT Function

Oct 21, 2008

I have a sheet that has consecutive dates in column A, from newest to oldest. I column B I have some stock returns. In column C I have some other stock returns. In D1, I want the covariance of the 2 stocks over the past x days (i.e. covar(B1:Bx,C1:Cx)). In D2, I want the covariance over x days as well, but only up to the date in A2 (so, covar(B2:B(x+1),C2:C(x+1))).

I tried this, but it doesnt work:

## Replace INDIRECT With INDEX Or Another Function

Jul 4, 2009

I am working on a spreadsheet which contains a number of reference data sheets (named “Reference data 2009”, “Reference data 2010” etc). As their names suggest, these sheets contain reference data applicable to the particular year. This reference data is used to perform various calculations in a “Calculations” sheet.
On the “Calculations” sheet, the user specifies the year for which they wish to perform calculations. At present, I am using the volatile INDIRECT function to perform various HLOOKUP calculations along the following lines:

=IF(D15>=HLOOKUP('Detailed net pay calculations'!D16, INDIRECT("'Reference data" & 'Detailed method'!\$C\$2 & 'Detailed net pay calculations'!D1 & "'!\$B\$43:\$Y\$52"), 4, FALSE), “Do something”, “Do something else”)

The reason I'm using the INDIRECT function is to identify the sheet with the appropriate year (hence "Reference data"&'Detailed method'!\$C\$2&'Detailed net pay calculations'!D1 which could be interpreted by Excel as "'Reference data 2009NEW" or "'Reference data 2010", depending on whether there's text in cell D1).

My query
The function I'm using is working perfectly fine but I am wondering if it is possible to replace the INDIRECT function (in red) with INDEX or another non-volatile function in order to reduce the performance impact (I have a fairly large number of these types of functions).

## Indirect Function For Lookup Table

Oct 18, 2011

I simply want to use the indirect function in vlookup formula in cell B2 (sheet11) is the sheet name I want to use for the lookup table.

=vlookup(B1,'[Alan.xls]sheet11'!\$A\$5:\$F\$19,4,0)

=vlookup(B1,'[Alan.xls]indirect("&B2&")"'!"\$A\$5:\$F\$19,4,0)

But doesn't work.

## Creating A UDF That Incorporates The Indirect Function

Apr 26, 2008

I would like to create a UDF that incorporates the Indirect Function. So if this is possible, I'd like to put in any cell..... =Tev(A2,D6)...... "Tev" would replace the Indirect function in the UDF script. In cell A2 would be the name of the sheet that I want to pull from. And Cell D6 would be the cell from the other sheet that I'd like returned.

## Worksheet Reference In Indirect Function

Feb 22, 2010

I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.

Using

I get a REF error because of the inclusion of the hyphen in the worksheet name.

How can I fix this without eliminating the hyphen?

## Indirect Function: Use Cell For Sheet Name

Dec 7, 2006

Cell F4 contains a worksheet name which was “calculated” by an IF function (IF something, then ‘T2’, otherwise ‘T3’).

Cell F5 must then look at either sheet T2 or sheet T3 (depending on what it is told to do by reference to cell F4 just above) and report what it finds in cell A1 there.

Clearly I must use the INDIRECT function. But I have tried every imaginable combination of single and double quotation marks to produce the equivalent of
=T2!A1
all to no avail.

Is it something to do with letter and number combinations?

Alternatively, how else should I achieve what I want?

PS F4 could have been “calculated” by any other means, e.g. =MONTH(TODAY()).