Reverse Lookup - INDEX() w. MATCH() - 2 of 2

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
6 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, this is going to be a practice task on index match match something that is quite often used for reverse lookup. Before we begin with the exercise, just a quick recap with the methodology that we were trying to apply index match match in index. The first parameter of the formula asks for array selection which we had referred by the term chess board mini chess board. The second two parameters talks about coordinates rule number first followed by column number, row number will be calculated vertically 123, top to bottom and column number will be calculated horizontally or sideways that is 123. Now the data structure would look like this. This will be the full chessboard the first match will focus on one column either this or this or this basically any one column where the common link is present.

And the second match will focus on horizontal or sideways movement now Keeping that in mind, let's look at this particular task. The table on the left, we have a table of employee IDs. That's here. The names are on the left hand side of the ID codes and gender age followed on the right hand side. Now looking at the ID codes, I want us to fetch the name. This could not have been done using v lookup, unless of course, you choose to cut this particular column and place it in the first column of the table array.

But assuming we are not able to do so we will stick to index match match equal to index. The first parameter array, which is the first chessboard let me choose the entire chessboard. This is our terminology in the discussion. Now, after having chosen the array, I'm going to press f4 to fix it, comma. Now I need to coordinate 14014 column number so I'm going to dedicate to match formulas one after another Now reserving a seat for each of the to match formula helps me avoid the syntax errors later point in time. Let me now focus on the first match.

The first match is going to look for an array which is lying vertically, top to bottom, and that is the ID code column within which what is the common link that's the ID code. So, match please look for 876 comma next will be lookup array, which means Junior follow senior the same principle which we had referred to in our earlier videos, that is to not start from before the start of the table or a selection of the index or later, it will start from the boundaries of the array selection. Now, making sure that it is fixed two comma zero to make sure it is an exact match that the match formula is trying to look for. next match formula look for any term that you are able to find in the header. So basically, you cannot look for 876 because 876 cannot be found in the header.

What can be found is the word name. Now, I'll have to fix this though I don't want this to move down as a copy paste the formula. So I'm going to fix this, comma. Next we'll be choosing the lookup array which this time is going to refer to a sideways movement. So the same concept applies Junior follow senior, not starting before, not starting later. and choosing from mobile number till the last cell value each.

I'm going to fix that to a four comma zero. Let me test whether the formula works in perfect. In fact, let me copy paste and see whether it works for the remaining two. Oh, yes, it does. In fact, when I change the header, let's say gender I get the gender as an answer each. There you go.

While number definitely correct. Now just an add on to this exercise, assume that if I press Ctrl R, at this instance, it quickly copies Paste the cell on the right. And if I choose name, I would want the formula which we wrote is equally applicable as a copy paste it right and down. But it doesn't. Let us discuss why. When I double click on the any, the first few things that I will notice on the screen is the colored borders.

The blue colored border, which is right now highlighted indicates the area of the chessboard or index. The purple one is indicating the first match lookup array, and the pink one is indicating the lookup array of the second match. Now what we also notice is these two lookup values, the first one actually has traveled from this cell to the next one, since it was not fixed. On the other hand, this particular cell mobile number was fixed Hence, it is not able to move to the next cell. So we need to rectify two things. One, we go back to the main formula.

First things first, this seafood I need to ensure that It remains in column C, but at the same time, it should be able to move to five and six. So I put my cursor around C four, I keep on pressing f4 until I get dollar C, followed by four, no dollars before four. This ensures that column C remains in column C. Next, D three, I want to ensure that when I copy paste the formula towards the right inside this d3 should be able to jump to E three. Hence, for that to happen, I need to remove the dollar before B, four. There you go. Now you might ask me, why am I putting the dollar before three, because I don't want that the match lookup value, which is currently pointing to the value in the cell of third row should be able to move down to fourth or fifth or six.

So be very careful. There's $1 c four, and this is the dollar three. Now let me just select the cell. All the array where I need to copy paste the formula. I'll press Ctrl D Ctrl r Hmm, now we get to perfect answer. And this was index match match for reverse lookup

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.