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&"'!A 2: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.

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&"'!A 2: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.

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&"'!A 2: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.

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 wrote in message
...
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&"'!A 2: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.