how to use variables in formula?

[RomeoLj]

Hello to every1. I am a new face here and a relative noob to excel.

Actually I use openoffice calc but its almost the same as excel...


my question, possibly a banal one for most here is:

how to make a part of the formula behave like variable?

example:

VLOOKUP(A1; 'SHEET1'.B1:C10; 2)

what i want to accomplish is this:

I want SHEET1 part to be variable, to be determined by value entered in another cell (say A2 in this .

so if i enter SHEET3 in A2, i want my formula to use that as a parameter inside VLOOKUP function.

I hope I was clear enough :/

 

[JoeMo]

If, for example, A2 contains the text (w/o quote marks) "Sheet1" then try:

=VLOOKUP(A1,INDIRECT("'"&A2&"'!B1:C10"),2,0)

NOTE: If your regional settings use the semicolon as an argument delimiter (as in your post), then replace the commas with semicolons.

 

[RomeoLj]

If, for example, A2 contains the text (w/o quote marks) "Sheet1" then try:

=VLOOKUP(A1,INDIRECT("'"&A2&"'!B1:C10"),2,0)

NOTE: If your regional settings use the semicolon as an argument delimiter (as in your post), then replace the commas with semicolons.

Hello!

Thanks for your help, it works!! Had to use semicolons but thats nothing.

Now I only have to comprehend why does it work ))

Maybe you can help again - what does the second value (0) behind '2' stand for in formula?

 

[aghaffar82]

The indirect formula is used which basically converts a text string to actual cell reference and the 0 behind 2 means false which is used to fetch an exact match of the lookup value.

 

[JoeMo]

Hello!

Thanks for your help, it works!! Had to use semicolons but thats nothing.

Now I only have to comprehend why does it work ))

Maybe you can help again - what does the second value (0) behind '2' stand for in formula?

You are welcome - thanks for the reply.
The 0 tells Excel you want to find an exact match to the lookup value. If you omit this argument Excel defaults to finding the nearest value that is less than or equal to the lookup value, provided the values of the first column in the lookup array are arranged in ascending order.