UNLOCK! Hidden Dynamic excel function never seen before | Dynamic sheet reference

To apply VLOOKUP with the INDIRECT function, you can dynamically construct the cell reference for the table_array argument of VLOOKUP. The INDIRECT function allows you to create a cell reference as a text string and then use it as a valid cell reference in another formula.

Here's an example of how to use VLOOKUP with INDIRECT:

Assume you have two tabs: "Sheet1" and "Sheet2". In "Sheet1," you want to retrieve a value from a tab specified in another cell using VLOOKUP and INDIRECT.

1. In "Sheet1," select the cell where you want the result to appear.
2. Begin typing the VLOOKUP formula. The basic syntax of the VLOOKUP formula is as follows:
```
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
```

3. For the lookup_value, enter the value you want to search for. This value can be in a cell or directly entered into the formula.
4. For the table_array, use the INDIRECT function to create a dynamic cell reference based on the tab name specified in another cell. Assuming the tab name is in cell A1, the formula would be:
```
=VLOOKUP(lookup_value, INDIRECT("'"&A1&"'!A1:B10"), col_index_num, [range_lookup])
```
The INDIRECT function concatenates the tab name (enclosed in single quotes) with the cell range (A1:B10 in this example). The & operator is used for concatenation.

5. Replace `lookup_value`, `col_index_num`, and the range A1:B10 with the appropriate values based on your specific case.
6. Press Enter to apply the formula.

With this approach, the VLOOKUP formula dynamically references the tab specified in cell A1 using the INDIRECT function. You can change the tab name in cell A1, and the VLOOKUP will automatically update to retrieve data from the specified tab.


**********FOLLOW ON**************
Facebook :- https://www.facebook.com/Excelworldofknowledge/
Twitter - https://twitter.com/SohanDey3
Instagram :- https://www.instagram.com/gyanontube/
Telegram :- https://t.me/Gyanontube
______________________
POWER BI
Power BI In Excel In hindi :- https://youtu.be/PfijkfjHyVQ
How to use Power Bi from beginner to advanced :- https://youtu.be/Efo4FSlbDL4
Power Bi में अपना काम सेव एंड रिकॉर्ड करे :- https://youtu.be/wJzXu_UUJ3s
Power bi tutorial for beginners :- https://youtu.be/1M2b8s4MvjY
Power BI dashboard :- https://youtu.be/uVT-56tNeOQ
Microsoft power bi in hindi :- https://youtu.be/kZ6WhghlV-E
Microsoft power bi course for free :- https://youtu.be/QO7RVnnFCsQ
Delete unwanted columns in power bi :- https://youtu.be/Kb5hTlY-wPk
Text to column in power bi for beginners :- https://youtu.be/nPJUQvXu85o
Power BI with New Icon in 2020 :- https://youtu.be/aZG553zp__s
Power Bi Reference option :- https://youtu.be/v3AkqXCENkc
Edit Data in power bi manually :- https://youtu.be/NkuqjjnAmsA
power bi tutorial for beginners in hindi :- https://youtu.be/WRAk1Z4sFIY
power bi dax tutorial for beginners :- https://youtu.be/vvL2N1sD34U
Power BI relationships between tables :- https://youtu.be/2hKkEqCg7Ss


Excel formula
How to use index match formula from Basic to advance :- https://youtu.be/ViQBcYTuYUg
How to make big formula in excel in hindi :- https://youtu.be/fRazd3EBk-w
Xlookup and Filter formula in hindi :- https://youtu.be/817Z8U2AREw
How to reduce excel file size :- https://youtu.be/M35_4TiOYz4
How to use Xlookup in excel :- https://youtu.be/hkKDM-zWZKA
xlookup formula in hindi :- https://youtu.be/hkKDM-zWZKA
array function in excel :- https://youtu.be/1BceH5sWZEo
New Xlookup with Search Mode :- https://youtu.be/9tjXVXo6xUw
Xmatch formula in excel in hindi :- https://youtu.be/KmFsYXRQvRM
how to use filter formula in excel in hindi :- https://youtu.be/4YDn2WEkUtw
Excel formulas and functions:- https://youtu.be/1GjbkV1bwL0
How to use excel unique function:- https://youtu.be/LcDJWcOEnyw
Sort formula in excel :- https://youtu.be/dPrvH3ijgPE


Pivot Table
pivot table from beginner to advanced :- https://youtu.be/1NEwwdgFvuI
Pivot table in hindi :- https://youtu.be/IDKiyf0Devk
How to use pivot table excel :- https://youtu.be/0zn7sNE5itc
Pivot table in hindi in excel :- https://youtu.be/F4llCqqM4wA
Drill Down in Pivot table excel :- https://youtu.be/XhPX9Ni9pMw
Pivot Graph in excel in hindi :- https://youtu.be/iXUloN0RiaA
how to use pivot table slicer :- https://youtu.be/zjtTSepL0QE
IF formula in Pivot table :- https://youtu.be/JpXTFn3n2F0
power pivot in excel :- https://youtu.be/DB3feMPUw6Q
how to link data in power pivot in hindi :- https://youtu.be/kj_p4dNcW6U
how to use power pivot in excel in hindi :- https://youtu.be/0W8um12jGMs

Gyan On Tube Excel