Vlookup for dynamic range :Indirect function:(mks503) part-3 (in hindi)

Опубликовано: 05 Декабрь 2018
на канале: Master Key of Success

Dear Friend you are welcome in "Master Key of Success".
You are watching
Indirect function in ms excel; Vlookup and indirect function: Auto range (mks503) part-3 (in hindi)

INDIRECT FUNCTION
Returns the reference specified by a text string.
References are immediately evaluated to display their contents.
Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax
=INDIRECT(ref_text,a1)

Ref_text : It is a reference to a cell that contains an (a) A1-style reference, (b) an R1C1-style reference, (c) a name defined as a reference, or (d) a reference to a cell as a text string.
If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
Note This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.
A1 is a logical value that specifies what type of reference is contained in the cell ref_text.
If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

=INDIRECT($A$1) Value of the reference in cell A1 (90)
=INDIRECT($A$3) Value of the reference in cell A3 (120)
=INDIRECT($A$4) If the cell A4 has the defined name "jan" the value of the defined name is returned (60)
=INDIRECT("B"&$A$5) Value of a reference in cell A5 (62)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

=INDIRECT("A10")
Formula used in this video
=VLOOKUP(C1,INDIRECT(C2),2,FALSE)
=VLOOKUP(C9, INDIRECT(C10),2, FALSE)
Thanks for watching
please like , share and subscribe it.
Watch following video on excel
 Create drop-down list (mks507)
    • Create drop-down list in ms excel (In...
 Dynamic and multiple function in a single cell (MKS506)    • Dynamic and multiple function in a si...
 How to auto link a data (mks505)
    • How to auto link a data in ms Excel |...
 Create a Dynamic Dependent Dropdown List (MKS504
    • Create a Dynamic Dependent Dropdown L...
 Vlookup for dynamic (mks503)    • Vlookup for dynamic range :Indirect f...
 indirect function (mks502)
    • How to use indirect function in ms ex...
 Indirect function (MKS501)
    • Indirect function in excel | Basic co...
 WATCH VIDEOS IN EXCEL (MKS78 )
    • HOW TO WATCH VIDEOS IN EXCEL FROM YOU...
 Convert English name into Other (MKS70 )
    • How to convert English name  into Hin...
 link or embed excel into powerpoint (mks69)
    • HOW TO LINK OR EMBED EXCEL INTO POWER...
 fill OMR sheet (mks69)
    • HOW TO FILL OMR SHEET & IMPORTANT INF...
 Insert Image Or Picture In Excel Comment Box (MKS68)
    • Insert Image or Picture in Excel Comm...
 Text to number or Convert to number (MKS67)
    • Text to number or Convert to number (...
 Auto highlight and auto scroll search box in excel(mks65)
    • AUTO HIGHLIGHT AND AUTO SCROLL SEARCH...
 Convert zero to dash in ms excel (mks64 )
    • CONVERT ZERO TO DASH IN MS EXCEL (MKS...
 Datedif (MKS63)
    • Datedif:How to Calculate  number of Y...
 Randbetween in excel(mks62)
    • Randbetween:How to generate random nu...
 Sumifs formula with multiple criteria in excel(mks61)
    • Sumifs formula with multiple criteria...
 Sumifs function in excel(mks60)
    • SUMIFS FUNCTION IN EXCEL IN ENGLISH(M...
 Sumifs formula in excel (MKS59)
    • SUMIFS FORMULA IN EXCEL IN HINDI (MKS...
 Wildcard characters in ms excel(MKS57)
    • How to use Wildcard characters in Ms ...
 Position of one text string within another text String (MKS56)
    • How to find starting position of one ...
 Position of a character in a Text string(MKS55)
    • How to find position of a character i...
 Number of characters of a Text or number or String (MKS53)
    • How to find number of characters of a...
 Find number of Characters in a Text String or Number (MKS54)
    • Find number of Characters in a Text S...
 Picture vlookup or image vlookup (mks52)
    • PICTURE VLOOKUP OR IMAGE VLOOKUP (mks...
 Create a picture lookup in excel (mks51)
    • How to create a picture lookup in exc...
 Change font color (mks50)
    • How to change font colour automatical...
 MID function(mks49)
    • How to extract a number or text or st...