INDIRECT() - Basics

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hi, in this video, we learn something about indirect formula, a formula which people rarely use. But once they start using it, they will find immense benefit in almost any formula. And gradually, you will see how powerful this formula is if you start using it in combination of other formulas such as we look up match, index, match match, or some ifs or if statement. But to understand this, we need to undergo some experiments in Excel, which we will allow. So we'll start with experiment number one. First, let's start with the first experiment.

Now, in the yellow cell, I'm writing a five. Now you gradually do why am I writing a five but at this moment, I'm writing a five. And I may ask you that if I write a formula below that, which points to the cell above, and if I press enter, what is going to be the natural answer? If I, I agree, this appears to be a very basic question. But now in contrast, whatever written right now, I am going to write a formula called indirect. Indirect.

I'm pointing to the yellow cell using the brackets. Let me first press enter and show you what comes out what gets displayed as a result, and then we'll discuss about it. Notice this time I'm getting the term blue. So basically indirect formula is asking the formula to go to a particular cell. And the cell may act as a desk, that desk contain an address chip, the address is containing the address which is a five and that's the reason it is taking the value from a file. To understand this through an illustration, you look at this particular diagram, a person who has been asked to go to an address the unless he finds that address chip, he will not be able to locate the address to which he must go to.

He is being directed to a desk, which contains the address CIT, in this case, Five, and using that a five or the cell reference or the address check, he's able to pinpoint the exact location to which he must go to. So basically we are going to relate this example with our experiment. That is, it x formula is asking you to go to a particular desk, which is v3, the cell v3, and the cell v3, which you will consider as a desk contains the address ID a five and address it leads us to the right location, which now contains blue. At this moment, if I write a six, enter black, if I write a one, enter, you get experiment one, and if right a to nothing zero, it's a blank cell. If I write a space one, notice I'm getting a reference error because this is an invalid address, the correct addresses a one now as a sub experiment, let's do one more task that is, I'm going to choose this particular cell and name this as color one with no spaces.

To the phenomenon of naming a cell or range, if you want to know more about it, please refer to our early videos. And at this moment after writing the name of the cell, I'm going to press enter. Similarly, I go to the next cell, and I'm going to give a nickname of color to color to and must make sure that I press Enter only and only then the name does get registered. So by now, color one is indicating the cell a five and a color two is indicating black. Now, if I write color one, isn't it a valid address for XL right now, if it is that I can effectively use indirect formula pointing to the cell, this is just the desk on which the address sheet is placed. And using that address of color one, it pinpoints me to the value of blue.

If you want to know more about the names you can also go to Formulas tab and look at Name Manager, because Name Manager will contain some of the name which I have recently given Some names which were given previously. Secondly, the other three names which are not required and just be content with the two for our experiment. This may seem theoretical but believe me after one or two more videos on indirect formula which are there in the series, this will be crystal clear

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.