how to use variables in formula?

RomeoLj

New Member
Joined
Nov 21, 2020
Messages
2
Office Version
  1. 2019
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 :/
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2020
Messages
2
Office Version
  1. 2019
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

Board Regular
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top