YTLE#62: VLOOKUP INDIRECT & Lookup in Different Tables

How do I correctly VLOOKUP to two different worksheet tables? I got so many #N/A's... :-(

=IF(($D2(<38443),VLOOKUP($A2,OldLookup!$A$2:$D$101,3,0),VLOOKUP($B2,NewLookup!$A$2:$D$101,3,0))

where 38443 is date value for 4/1/2005.

If colum D (date field) is less than "4/1/2005" VLOOKUP to worksheet 'OldLookup' else VLOOKUP to worksheet 'NewLookup'.

I have a workbook that is used to calculate the costs for a range of different business scenarios. Each scenario is defined by setting a range of variables and is summarised in a fairly straightforward table. The sheet with the results table is then moved to a separate workbook with a name such as 'Scenario1'.

I repeat the process with a new settings on the variables and that sheet is moved across as 'Scenario2'.

I want to be able to use a third sheet to simply show the differences beetween the two scenarios. The difference table would look much the same as the 2 scenario results but with the difference shown in each cell.

I want to be able to add further scenario reports to the workbook (scenario3, 4 etc.,) and I'd like to be able to use my difference sheet to be able to show the delta between any pair of scenarios - ideally by entering the names of the scenarios into 2 cells on the Differences worksheet.

I'm hoping that there is a way of doing this with the INDIRECT function but I can't crack it.

Any assistance would be appreciated.

I'm relatively new to using Pivot tables. I have the following code set up right now:

=GETPIVOTDATA("Average of OFF",Pivots!A2,"H/R","R","W/L","L")

It works fine. However, I want to use indirect, so that I can make the "A2" a link instead of hardcoded (so I can get this data from diff pivot tables.

I coded it using INDIRECT so it would use the Pivot table From Column A and the Row listed in "C1"; however, with all the Quotation Marks, it's getting confused. Any help?

=INDIRECT( "GETPIVOTDATA("Average of OFF",Pivots!A" & C1 & ", "H/R", "R", "W/L", "L")")

^doesn't work, obviously.

Hi,

I am using a lookup formula to pull details from the cells above. Will always be the range directly above the current cell (to avoid circular reference)

At present I am using :

=INDEX(INDIRECT("C15:C"&ROW()-1),MATCH($A194,INDIRECT("A15:A"&ROW()-1),0))

But it means whenever I pull it across to another column, I need to edit the text elements of the INDIRECT. Would like to be able to use COLUMN() in some fashion

Any thoughts?

I have been provided by 100 sales invoice by a client done on spreadsheet. I now have a 101 sheet workbook. The first 100 sheets are the individual invoices, and the last sheet is a summary sheet. I want to list the net,vat and gross from each invoice onto the summary sheet. Normally, the net, vat, and gross would be in the same cells on each sheet and i would use
=INDIRECT(A1&"!H3") where cells A1-A100 listed the sheet names

However, the net, vat, and gross are not in the same cells on each sheet. The only consistency is that the 'net amount' is 2 columns to the right of an entry 'subtotal' in column F.

Is there a way of doing some sort of lookup in column F for 'subtotal' and returning the value 2 columns to the right and placing it inside the INDIRECT STATEMENT

Can anyone tell me if there is a difference in the speed at which a workbook will update depending on whether you use the lookup or the indirect function?

One of my projects is going much slower now that I've introduced a lot more indirect functions and I was wondering if this might be the cause of it

Cheers
free

i have vlookup(a1,indirect(b1),2,0
the value in b1 however is a range in another open workbook
[data.xls]setup!a1:b1000

i cant seem to get it to work, any ideas?

thanks in advance

Hi all,

I have the bellow formula

=LOOKUP(2^15,SEARCH(INDIRECT(B2),D2),(INDIRECT(B2)))

Serutns N/A

If I take out the indirect and use just the ranges it works fine.

Dear all,

I am using a formula that indexes and matches from another sheet (2006). It retrieves all the latest values.

=INDEX('2006'!B9:B375,MATCH(9.99999999999999E+307,'2006'!B9:B375))

However, I have created lots of different sheets with years and the only way I have found to update this formula is to manually change it from, say, '2007','2008' etc. I want this to be dependant on the year, however, e.g. YEAR().

I can't seem to add a reference to a cell with a year though. Would anyone be kind enough to give me some advice?

Regards,
Robin

So 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:

Code:

'H:\%location%\[file.xls]Main Template'!$A:$CG

And just to set it up I have this vlookup code:
Code:

=VLOOKUP(B2,(INDIRECT("$O$2")),INDIRECT("O4",TRUE),FALSE)
O2 - Value of "DIR'!$A$1"
O4 - Value of "18"

I did a windows search to find one of the items and changed the value of O2 and I got #N/A as the result.

Please direct me properly so I can get this taken care of.

Thank you in advance for your help.

-Nic