# INDIRECT Function To Lookup Values On A Range Of Other Worksheets

Jan 17, 2014

I am trying to use the INDIRECT function to look up values on a range of other worksheets. I have a column of data in col A which is essentially a lot of different worksheet names. On each worksheet I need to use VLOOKUP to find a value.

Easier to show formulas. This is an example of what I want to recreate:

=VLOOKUP(O2, test!B3:C13,2,FALSE)

I want to replace the text "test" with the text in column A. So the first INDIRECT formula looks like this:

=INDIRECT("VLOOKUP(O2, " &A1& "!B3:C13,2,FALSE)")

Which to me looks like it should work but I get a #REF! error. I've tried various permutations, e.g. having the INDIRECT part of the formula located in a different place (next to the "test" text) but run in to similar issues.

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

## Lookup Data From Multiple Worksheet Using Indirect Address And Match Function

Sep 28, 2011

determining the correct formula to lookup various details from multiple worksheet:

Data as follows:
Sheet1: input
AB1MonthSales2AUG55003MAY4000

Sheet2: Data

AB1MonthSales2JAN20003FEB25004MAR30005APR35006MAY40007JUN45008JUL50009AUG550010SEP600011OCT650012NOV700013DEC7500

i dont know what is the correct range inside the match fuction to identiy the row number inside the Data sheet, i think the solution would be match(a2,Data!a2:a13,0) but things might get complicated if you have multiple worksheet such as sheet2: Data , sheet3: Data2, sheet3: Data 3

and each table has unsorted details compared to others sheets (Data vs Data 2)

## Indirect Formula Syntax For Referring To A Range On Two Worksheets

Jul 5, 2012

B1=Name of WorkSheet
B2=Name of 2nd Worksheet

I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.

=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))

with some added spaces for clarity:

=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))

## Indirect Function To Reference A Named Range

Apr 30, 2009

How would I used the indirect function to reference a named range in the workbook in formula creation.

For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:

=sum(indirect(A1))

In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.

I'm using Excel 2007.

## Lookup Function/Formula Across Multiple Worksheets

May 14, 2008

This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.

=IF('2'!G3:G271=A3, LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)

## Set Up A Lookup Function With Two Lookup Values?

Apr 18, 2008

Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:

1 A 14
1 B 22
2 C 84
4 D 25

I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.

## MID Function For Different Lookup Values?

Mar 26, 2014

In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.

What would be the best way of getting the invoice number?

## IF(AND Function Using LOOKUP For Conditional Values

Dec 3, 2013

I'm new to excel and am attempting to use an IF(AND function that requires the use of two LOOKUP tables for the IF conditions, I have been unable so far to generate the answer I'm looking for.

I have a set of data in two columns, column A which has a series of dates from 1/09/2013 - 30/09/2013 (multiple for each date), and column F which has a series of 4 digit numbers (employee numbers). I also have two columns where I can enter in values for LOOKUP tables, one column for dates (O2:O4) and another for employee numbers (Q2:Q17).

What I am trying to achieve is a True/False answer in a final column for IF A2 = value from column (table) O2:O4, AND F2 = value from column (table) Q2:Q17. For the querry to be true the values in column A and column F must meet both conditions, be from a date in column O2:O4 and also be an employee from column Q2:Q17

=IF(AND(A2=LOOKUP(A2,O2:O4),F2=LOOKUP(F2,Q2:Q17),"Yes","No")

Currently I get a #NA error and I know its due to my formula not being anywhere near correct, as far as I can tell it's a result of the LOOKUP part of the formula I'm attempting to create, as it generates a #NA area when the data does not meet the criteria in the columns (tables), I have attempted to include a ISNA section to the formula to alter the result if the data does not meet the two conditions but when applied to the whole data set it only responds with a False answer even if the data meets both conditions.

## Lookup Function With Descending Values Vector

Dec 24, 2009

Is there a way to use the lookup function in reverse? The "lookup vector" on mine has to be descending, that is it it is a column going from high values at the top of the page to lower values at the bottom of the page. When using lookup function in this setup, excel gives me to wrong answer for some reason. If I make the lookup vector ascending, it works, but unfortunately the vector has to be descending in my worksheet.

## Set A Range To Use In A Lookup Function

Apr 10, 2007

I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.

If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection

If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.

The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.

## Function To A Range Of Cells Over A Range Of Worksheets

Mar 31, 2009

I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.

I've been working on this one for a couple days now and I'm not making much progress.

## Formula / Function To Pull In Multiple Lookup Values

Aug 21, 2014

I am trying to replicate a payslip from a list of data on a worksheet.

The list of data contains the employee name, location they worked, and number of hours.

Each employee will work at multiple locations throughout the month, perhaps 10 or so.

The payslip must contain each location worked along with the relevant data, in a list so to speak.

What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?

## Lookup Type Function Returning Multiple Values

May 23, 2007

I have attached a sample spreadsheet which describes my problem.

In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.

## AND Function Date Range Lookup

May 20, 2008

I am trying to an If statement but the first part of the IF statement needs to be between a specific date range. Here is the formula I have:

=IF(AND(Sheet1!A4>=1/1/2008,Sheet1!A4

## Lookup With Indirect?

Dec 5, 2013

How could I create a formula that would look up based on month, category, and most importantly an indirect? I have attached a spreadsheet, the indirect is in K13 (and could be Quarter 1, Quarter 2, Quarter 3, Quarter 4) with matching data in "Sheet 2".

## Indirect Sum Through Worksheets

Sep 4, 2009

I am trying to sum through multiple worksheets but maintain flexibility using INDIRECT but it is not working!

I have a worksheet for each month of the year Jan - Dec with a financial result. In order to get a Year To Date figure I would have a formula such as:

=sum(Jan:Jul!B3) for a July YTD.

However, I want to maintain flexibility such that I can enter the worksheet name in cell A1, e.g. Sep and then have a formula such as:

=sum(INDIRECT("Jan:"&A1&"!B3"))

Thus allowing me to generate the correct YTD at any point. All I get is a #REF error.

## Lookup Across A Sheet VBA Indirect?

Dec 12, 2011

I've tried to find the solution and the options I've tried I just can't get the code or syntax correct...

I have a workbook with 11 sheets in, 10 data sheets, and a summary

On sheet call "CB", I want to select any cell, then, via VBA, on sheet "Summary", in cells b4 to b14, where a4 to a14 have the sheet names, I want to reference the cell I selected, using the Indirect function....

something like

Code:

SelCell = Activecell.Value
Range("b4").Select
Activecell.FormulaR1C1 = "=INDIRECT(""'""&R[0]C[-1]&""'!SelCell"")"

I know it's incorrect.

## Indirect Sum Across Multiple Worksheets

May 24, 2007

I have the exact same problem as was posted in the past, in this post:

I am trying to sum across a dynamic range of worksheets: sum(sheet1:sheet3!A1). I would like to have "sheet1" and "sheetX" in a separate updateable cell and refer to these for the sum:

Basically, do something like:
Cell A2 = Sheet1
Cell A3 = Sheet3
sum(concatenate("Sheet1:":"&indirect(A3)&"!A1"))

When I use the following equation I get a "#REF" error(posted as a solution in the earlier thread):=SUMPRODUCT(--N(INDIRECT(" Sheet"&ROW(1:3)&"!A1"))) what is the "--N" for. Is there something that I should add?

## Lookup Between A Range Of Values

Apr 8, 2008

I have a workbook where road structure data is recorded i.e. bridges, culverts, etc.

One of the worksheets has data I need to lookup. The primary worksheet called “Structure_Data” has the ‘Road Number’ (RN) of a particular road and the ‘Road Running Distance’ (RRD) of a particular structure. A particular road may have many structures along its length.

Another worksheet called “Road_Number_of_Lanes” has data that that indicates how many lanes there are between particular RRDs.

Here is what I am trying to work out...
In the “Structure_Data” worksheet look at the RRD of a structure and the number of the road it is on, then look up the road number on the Road_Number_of_Lanes worksheet; look at the ‘Start RRD’ and the ‘End RRD’ and return the number of lanes for that particular RRD.

“Structure_Data” worksheet
Column C has the Road Number
Column X has the Road Running Distance

Column A has the Road Number
Column C has the Start Road Running Distance
Column D has the End Road Running Distance
Column E has the number of traffic lanes

My problem is that for example Road Number 1000 starts at 0 and goes to 927.09 (approx 927 km long). There are 27 entries of RN 1000 i.e. from 0 to 411.69 there are 2 lanes, from 773.62 to 774.85 there are 3 lanes and from 774.85 to 778.09 it goes back to 2 lanes.

How can I find out how many lanes of traffic there is at a particular structures location.

## Return Cell Address That Falls Within A Range Of A Custom Lookup Function

Jul 3, 2009

I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

## Lookup Two Values In A Data Range

Mar 1, 2014

I have two values I need to find in a chart.

(See Attached)

I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.

## Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009

I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer

For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function

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

## Lookup Range Of Values In Array And Sum If Positive

Mar 21, 2014

I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:

=SUMIF(M12:M600,">0",(VLOOKUP(M1:M2,K12:M600,3,TRUE)))

but cannot seem to get it to work.

## Convert Selected Range On All Worksheets To Values

Aug 9, 2007

I have a macro that changes user selection from formulas to values:

Dim vCol As Variant

vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value

End Sub

I have several workbooks that use this macro, and the workbooks can include several sheets.

Is there's an easy way to change the macro so the user selection is changed in all sheets in the workbook. E.g. if the user selection is column H, the formula is changed to values in all sheets in the workbook.

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

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

## How To Use Indirect Function Within Sumproduct

Dec 29, 2009

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