VLookup and Indirect

I am currently using INDIRECT to get the lookup_value for VLOOKUP

The formula I am using is:
=VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,)

where D2 is a text string  (example:Akan 1-1)

When I use the above formula I get #Ref
If I place D2 in quotes:
=VLOOKUP(INDIRECT("D2"),INDIRECT("'"&B2&"'!A2:F20"),2,).

The correct result is returned.  However this locks the reference to D2.  I 
need to be able to use this code so that If I move it to the 3rd column it 
will read D3 and I would prefer not to change the number each time.

I have also tried the following formulas and they all return #Ref for the 
lookup_value.  
=VLOOKUP(INDIRECT("""&D2&"""),INDIRECT("'"&B2&"'!A2:F20"),2,)
=VLOOKUP(INDIRECT(""""&D2&""""),INDIRECT("'"&B2&"'!A2:F20"),2,)


I used the formula auditing to determine where the problem is the 
lookup_value.  The rest of the formula works just fine.

Thanks in advance for the help.
 
0
Utf
11/23/2009 4:44:06 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1811 Views

Similar Articles

[PageSpeed] 26

Try
=VLOOKUP(D2,INDIRECT("'"&B2&"'!A2:F20"),2,0)

If this post helps click Yes
---------------
Jacob Skaria


"Will Cross" wrote:

> I am currently using INDIRECT to get the lookup_value for VLOOKUP
> 
> The formula I am using is:
> =VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,)
> 
> where D2 is a text string  (example:Akan 1-1)
> 
> When I use the above formula I get #Ref
> If I place D2 in quotes:
> =VLOOKUP(INDIRECT("D2"),INDIRECT("'"&B2&"'!A2:F20"),2,).
> 
> The correct result is returned.  However this locks the reference to D2.  I 
> need to be able to use this code so that If I move it to the 3rd column it 
> will read D3 and I would prefer not to change the number each time.
> 
> I have also tried the following formulas and they all return #Ref for the 
> lookup_value.  
> =VLOOKUP(INDIRECT("""&D2&"""),INDIRECT("'"&B2&"'!A2:F20"),2,)
> =VLOOKUP(INDIRECT(""""&D2&""""),INDIRECT("'"&B2&"'!A2:F20"),2,)
> 
> 
> I used the formula auditing to determine where the problem is the 
> lookup_value.  The rest of the formula works just fine.
> 
> Thanks in advance for the help.
>  
0
Utf
11/23/2009 5:13:04 PM
That worked Thanks

I guess that's what I get for making it complicated.

"Jacob Skaria" wrote:

> Try
> =VLOOKUP(D2,INDIRECT("'"&B2&"'!A2:F20"),2,0)
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "Will Cross" wrote:
> 
> > I am currently using INDIRECT to get the lookup_value for VLOOKUP
> > 
> > The formula I am using is:
> > =VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,)
> > 
> > where D2 is a text string  (example:Akan 1-1)
> > 
> > When I use the above formula I get #Ref
> > If I place D2 in quotes:
> > =VLOOKUP(INDIRECT("D2"),INDIRECT("'"&B2&"'!A2:F20"),2,).
> > 
> > The correct result is returned.  However this locks the reference to D2.  I 
> > need to be able to use this code so that If I move it to the 3rd column it 
> > will read D3 and I would prefer not to change the number each time.
> > 
> > I have also tried the following formulas and they all return #Ref for the 
> > lookup_value.  
> > =VLOOKUP(INDIRECT("""&D2&"""),INDIRECT("'"&B2&"'!A2:F20"),2,)
> > =VLOOKUP(INDIRECT(""""&D2&""""),INDIRECT("'"&B2&"'!A2:F20"),2,)
> > 
> > 
> > I used the formula auditing to determine where the problem is the 
> > lookup_value.  The rest of the formula works just fine.
> > 
> > Thanks in advance for the help.
> >  
0
Utf
11/23/2009 5:20:01 PM
Hi Will
On Sheet1 I have this data starting in A2
XXX    Sheet3    XXX    a4    3
cat
dog
----
On Sheet 3 starting in A3 I have
cat   1   4   7   10   13
cow   2   5   8   11   14
dog   3   6   9   12  15
mule  4   7   10   13  16

The formula in E2 that returns the value 3 is
=VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,)

Of course, I could use
=VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,FALSE)
and then the animals would not need to be in alphabetical order.

Changing the value in D2 from a4 to a3 give a 1 in E2, while a5 give a 4.
So your formula is working without quotes around D2

Hard to say why you have trouble without seeing your data.
Feel free to send me a sample file - get my addy from my website.
Please copy your original message to the private email you send me
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Will Cross" <Will Cross@discussions.microsoft.com> wrote in message 
news:234119FF-127D-4C2C-BC38-DE322F5737E3@microsoft.com...
>I am currently using INDIRECT to get the lookup_value for VLOOKUP
>
> The formula I am using is:
> =VLOOKUP(INDIRECT(D2),INDIRECT("'"&B2&"'!A2:F20"),2,)
>
> where D2 is a text string  (example:Akan 1-1)
>
> When I use the above formula I get #Ref
> If I place D2 in quotes:
> =VLOOKUP(INDIRECT("D2"),INDIRECT("'"&B2&"'!A2:F20"),2,).
>
> The correct result is returned.  However this locks the reference to D2. 
> I
> need to be able to use this code so that If I move it to the 3rd column it
> will read D3 and I would prefer not to change the number each time.
>
> I have also tried the following formulas and they all return #Ref for the
> lookup_value.
> =VLOOKUP(INDIRECT("""&D2&"""),INDIRECT("'"&B2&"'!A2:F20"),2,)
> =VLOOKUP(INDIRECT(""""&D2&""""),INDIRECT("'"&B2&"'!A2:F20"),2,)
>
>
> I used the formula auditing to determine where the problem is the
> lookup_value.  The rest of the formula works just fine.
>
> Thanks in advance for the help.
> 

0
Bernard
11/23/2009 5:53:40 PM
Reply:

Similar Artilces:

use VLookup to copy more than 1 cell
I have been using VLookup in Excel 2003 for some time but I would like to expand the VLookup results. Instead of just copying one cell to paste as the result of the formula, can I have VLookup copy and paste a series of cells from same row of the found item? Thanks and have a great day!!! :) You can change the result of the VLOOKUP function by changing the 3rd arguement in the function, which determines which column from the table to return. =VLOOKUP(Find_this,In_this_table,#_of_column_to_return,Closest_Match?) If you want to copy and paste this and have it adapt/change, u...

Help with IF then ELSE and VLOOKUP
I have the following dilemma. I have a Vlookup, which tests for values between 110 and 131 and allocates an appropriate bonus that works fine. If the values are less than 110 and greater then 130 the resultant cell displays #N/A as expected. However I have now been advised that if I have a value that is less than or equal to 110 I need to allocate a bonus of 86.49 and if it is greater than or equal to 131 I need to allocate a bonus of 102.00. Any value between 110 and 131 should invoke the vlookup to allocate the appropriate bonus. I was hoping to using an ‘if then else’ statement to s...

One to many vlookup
Hi All I have two sheets, here is an example of what I am looking at: Sheet 1: Scenario: Number 1 Number 2 Number 3 130 10 10 33 etc. Sheet 2: Scenario: Number: 1 2067896512 1 2098172651 1 4258712369 10 4258097209 10 5783992762 20 1891237762 33 2349785432 33 2131452332 33 9843527232 130 4354678976 130 7836284285 etc. I would like to h...

Vlookup returns
Hi, Is there a way of getting VLOOKUP to skip the first value it finds and returns a subsequent one? I have a worksheet (#1) constructed something like: Unique ID# ID# Data A1 1 Unique data 1 A2 2 Unique data 2 A3 3 Unique data 3 A4 2 Unique data 4 A5 2 Unique data 5 A6 1 Unique data 6 Here, the ID# may repeat, but I have no way of knowing how many times, and therefore do not know how many...

VLOOKUP driving me crazy
Hi all - I have a VLOOKUP formula that is really bugging me. Instead o reading accross it seems to be reading down. The formular is =VLOOKUP(A1,OCU_VCU!W1:X20,1) I cant see whats wrong with it - ive used VLOOKUP so much in othe projects - Im either really missing something or excel has gone funny. Thanks for any comments -- LB7 ----------------------------------------------------------------------- LB79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1215 View this thread: http://www.excelforum.com/showthread.php?threadid=49305 You aren�t giving a huge am...

how do i use an if statement with a vlookup
I have a range named "JDE"(A2:D40) that houses a column of PO#s (B2:B40), a column of Model #s (C2:C40), and a column of Serial #s (D2:D40). I have another worksheet with a range named "Bren" (E2:H50), that houses a column of PO#s (E2:E50) and a column for the serial number if the model # on JDE matches a given model # for that column (ie: column F, holds only serial numbers for model SAS1000, column G holds only serial numbers for model FRE2121. How can I write a statement that looks up the corresponding PO# in JDE, then enters the serial number only if the model...

Replacing export with descriptions -Vlookup Macro
I have a monthly report that I export from an old system. Column A contains a number and I have been using VLookup to reference the description of the number from Worksheet2 Column A, B with Column B listing the description. Is there a way of creating a macro to do this? If so, will I still have to create an empty column for the result or can the macro replace the data in the cell? Thanks! Hi Joey, You can write a macro. The macro could replace the cell containing the number. Something like this: Sub mytest() For Each cell In Selection cell.Value = WorksheetFuncti...

VLookup question #3
I've tried searching for an answer to my question, but with no luck. I'm working on a sheet to compare to lists of numbers, using this formula: =VLOOKUP(C6,A4:B392,1,TRUE) I've tried to use a constant in the formula, but it doesn't work. Assuming that {} defines a constant, why can't I do this? =VLOOKUP(C6,{A4:B392},1,TRUE) so the array I need to search is constant? Peter, You need absolute cell references ($A$4:$B$392), not relative (A4:B392). The F4 key will switch this, if the reference is selected while you're in Enter or Edit mode. =VLOOKUP(C6,$A$4:$B$39...

Vlookup Question 04-21-10
Hi Every One, I hope you all are doing well, I required your help to resolve my issue in excel. On single worksheet i have two different sheet name as 1.IP Range 2. Result. Question: EG:IP Range Start IP End IP Area 192.168.1.4 195.182.254.254 AG 10.15.33.10 10.18.56.254 EMEA 10.128.33.5 10.132.40.60 AP Here it will Continued as well. On Result sheet i required result like: IP Area 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 EMEA 10.170.255.255 NA 10.129.36.8 AP I required result on area column. Here we have more than 50000 IP's but very difficul...

VLOOKUP output
Hi. I need to use an if formula If the VLOOKUP formula in the "list" sheet doesn't find any information in the "price" sheet, I want to use an IF formula to get information from another column in the "list" sheet. But if the VLOOKUP formula cant find any information in another sheet but the output is (in swedish) #saknas! (I think it returns #N/A in english) so I cant use the IF formula. VLOOKUP should be ideal for that. Don't use IF. Please post your formula and inputs. Is the lookup table sorted? -- Kind regards, Niek Otten Mi...

Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)
I have a sheet named "lookUp" that has 3 columns emp No Name Mobile No. 11111 Bruce, Jack lincoln 0404 126 561 and I have been given this:- =VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) Which takes the info from the "LookUp" sheet to another sheet and puts the name in to a cell. But a new "LookUp1" sheet is laid out as emp No Surname First Name Mobile No. 11111 Bruce Jack lincoln 0404 126 561 How can I change the Vlookup to now put both the Surname PLU...

a double vlookup, is it possible?
Hi there, and thank you for reading this :) I have 2 pivot tables. Pivottable 1 has 3 columns; Type of item, Price of each item, Tota sold in this transaction Pivottable 2 has 3 columns; Type of item, Price of each item, Profit o each item these are 2 pivottables, as these are from 2 access databases, th first one is called transactions, and pivottable 1 is from tha database, and the other one is called sell orders, and pivottable 2 i from that database. What I want to do is in a new column next to pivottable 1 lookup th type of item from pivottable 2, see that it matches the type of ite...

VLOOKUP
Hi All I have the following formula: =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)) which works fine. Problem: How do I stop the value *0* displaying, if there is no data in worksheet List! column 7 ? Many thanks George George, =IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L ist!$A$1:$G$500,7,FALSE)) -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "George Gee" <george.nomaps@ntlworld.xom> wrote in message news:Otk8AZ4$DHA.1...

Lookup, vlookup, find, or what
Thanks in advance. Xcel07 on WinXP. I have a list of names in sheet 4, I need to search sheet 1, 2 and 3 to see if each name on sheet4 is located on any of the other 3 sheets. Here are a couple of my attempts so far =LOOKUP(C3,'Friday 930am:Monday 1130am'!C2:C25) =SUMPRODUCT(C2*'Friday 930am:Monday 1130am'!C2:C25) To clarify the above Sheet 1 is named Friday 930am Sheet 2 is anmed Friday 12:30pm Sheet 3 is named Monday 1130am Any suggestions are welcome You can't do that type of 3D referencing. Need to individually look through each sheet =ISNUMBE...

vlookup to copy data from another tab
I have two tabs in spreadsheet. MAIN & THIRD PARTY I'm trying to update third party contact information from THIRD PARTY to MAIN by entering a POST CODE (ZIP CODE) into MAIN and for the formula to search the THIRD PARTY tab and return the name, address and contact info there. Basic info in THIRD PARTY covers NINE columns that should be copied and transferred to MAIN if the post code matches. I can get it to look but not to search the columns for the variable. I have sorted the spreadsheet into into order. Thanks in advance fro your help. David Are you using V...

VLookup Function over Multiple Worksheets
I am trying to use the VLOOKUP function over multiple worksheets, can this be done on Excel 2003? I am trying to automatically retrieve data that is dependent on previously entered fields in drop down lists that I have created. Regards, A semi-exoctic formula from Peo S a few years ago, this looks up across eight sheets. It is an array-entered formula... CTRL+SHIFT+ENTER If you wnt to tackle this I will help you, I don't completely understand the formula but I believe I can guide you through it to lookup over many worksheets. (formula is all one one line in both cases...

Vlookup in VBA
I have to do a vlookup with VBA in one worksheet in which the Table_Array will go from column A11 to L11 down to a row, containing a specific text, "Paid." is that possible Hi please stay in your existing thread -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > I have to do a vlookup with VBA in one worksheet in which the > Table_Array will go from column A11 to L11 down to a row, containing > a specific text, "Paid." is that possible Why not keep to the original thread and respond to Frank's suggestion? -- HTH RP "Jeff" <Jeff...

Avoid volatile Indirect problem
I have a cell formula in my spreadsheet =INDIRECT("'http://www.domain.com/" &$B$2&"/[results.xls]results'!$e2") &$B$2& rfefers to the second part of a URL which changes as B21 changes its value The spreadsheet functions perfectly except that it goes incredibly slowly because INDIRECT is volatile and there are 120,000 occurences of this formula or a variation on the same. Anyone know a way around this or an alternative to INDIRECT that does the same thing? Many thanks tp how about a nice looping macro for i=1 to whatever number cells(i,...

vlookup #44
I need a way of testing the return value from a vlookup call. It will happen that the lookup will fail and the return will be #N/A, which is not nicest thing to see on a spreadsheet! I have tried "if" statement variations but testing the cell is unsuccessful as the 'value'of the cell is not what is displayed! Jim Crawford Alden Press Oxford UK jcrawford@alden.co.uk To return an empty cell if a match is not found: =IF(ISNA(MATCH(D1,A1:A100,0)),"",VLOOKUP(D1,A1:C100,3,0)) -- HTH, RD ============================================== Please keep all corresponden...

Vlookup w/ VB
Hello, I have 2 worksheets like this: Worksheet A Worksheet B Row # Column A Row # Column A Column B 1 Paid 1 Paid $100.00 2 Paid 2 Paid $50.00 3 Paid 3 Paid $80.00 4 Paid 4 Paid $100.00 5 Paid 5 ...

Vlookup?
I am trying to sum the cells in column "AY" for each row that has a positive value in column "S" I am very confused and this is what I have so far. Can you help me please? =SUM(VLOOKUP($S$3:$S$502>0,$S3:$AY502,33,FALSE)) Try =SUMIF(S3:S502,">0",AY3:AY502) Hope this helps, Hutch "Doug" wrote: > I am trying to sum the cells in column "AY" for each row that has a positive > value in column "S" > I am very confused and this is what I have so far. Can you help me please? > > =SUM(VLOOKUP($S...

Vlookup with a specific item
Hi, I would like to vlookup on employees' degree start date, eg.: A B C emp. no. Start date Edu 1 01/01/90 Dip 1 01/01/95 Deg 2 01/01/82 Cert 2 01/01/86 Dip 2 01/01/90 Deg How do I vlookup on the emp. no and deg start date? Thanks in advance. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice ...

VLookup #34
Hi, i just joined this group. I am new to excel and am trying to match trades from two clients. The problem is that the trades are named differently and thus require some type of pivot table or vlookup to solve the problem. I would appreciate if someone could give me some advice on how to do this. I am sure there is a simple approach, but I have been unable to figure it out. Thanks I would suggest that you post some example data, particularly of the items which are named differently, then someone may be able to help you. Pete VAnand27@gmail.com wrote: > Hi, i just joined this group. I ...

Vlookup returns incorrect match
I am using vlookup to match values on truck numbers on sheet 1 with truck districts on sheet 2 and excel is returning the #N/A error and I can't find the problem. For example On sheet 1 the truck numbers are in column C formatted as text and the truck numbers consist of text and numbers (i.e. T1767A42) On sheet 2 the truck numbers are in column A, sorted ascending, formatted as text, and are the same numbers as on sheet one. (T1767A42) Using cell d2 on sheet 1 for my vookup statement I inputted =Vlookup(C2,sheet2!A2:C547,2,False) If I leave the range looup as True excel returns the l...

Vlookup from First Three Characters of Codes
I have posted this on Excel WorksheetFunctions. No help yet.. therefore posting it here.. Can somebody help me Thanks Hello All, I am using Windows XP/Office 2003 and have the following problem: I have a Sheet Name 'Customers' with a Dynamic Range defined as 'Codes' (Column A and Column B) as follows: A B APL Astro Plastics Limited AFC Allan Feed Company ..=2E. ..=2E. ..=2E. etc etc In another Sheet I use Vlookup to extract values using the following formula: =3DIF(ISNA(VLOOKUP(A2,Codes,2,FA=ADLSE)),"",(VLOOKUP(A2,Codes,2,F=ADALSE))) ...