Vlookup+ two way lookup(two di

[Link]
Index+Match+Match
Vlookup+Match
Chocolate Strawberry Vanilla q1. What is the value of 1st row and 3rd column?
Jan 544 639 189 Q5
Feb 217 719 679 q2. What is the value of 11th row and 2nd column?
Mar 810 178 810 q3. What is the position of the sale value 639 of Strawberry?
Apr 567 926 929
May 745 230 364 q4. What is the position of the sale value 871 of Choclate?
Jun 298 820 947
Jul 457 522 832 q5. What is the value of sale of Choclate in
Aug 495 500 239
February?
Sep 871 391 529
Oct 585 225 791
Nov 478 262 540
Dec 741 883 809
olumn? Chocolate Jan 544 Q1 189
StrawberryJan 639 Q2 262
column? Vanilla Jan 189 Q3 1
639 of Strawberry? Chocolate FEB Q4 9
StrawberryJan Q5 217
871 of Choclate? Vanilla Jan
Chocolate Jan
f Choclate in Strawberry
Vanilla
Chocolate
Strawberry
One dimensional -Index+MATch+MATCH if you are looking for Two Values {=INDEX($D$3:$D$9,MATCH(G7&
First Name Last Name Salary First Name James
James Smith James Smith $64,901 Last Name Anderson
James Anderson James Ander $70,855 Salary $70,855
John Lewis John Lewis $97,566 What is the salalry of James Anderson?
John Walker John Walker $58,339 Lookup 1 James VLOOKUP does not work with mo
Mark Reed Mark Reed $125,180 Lookup2 Anderson
Richard Lopez Richard Lope $91,632 70855 {=INDEX($E$3:$E$9,MATCH(H7&

[Link]

How to solve the problem of Vlookup q1. Lewis

Salary John Lewis

q2. James Looking for

Smith Looking for
Salary 64901 required value
=INDEX($D$3:$D$9,MATCH(G7&G8,B3:B9&C3:C9,0))}

VLOOKUP does not work with more than one lookup value.

=INDEX($E$3:$E$9,MATCH(H7&H8,B3:B9&C3:C9,0))}

q1. what is the salary of Lewis?

Q2. What is the salary of James Clark?

equired value
TABLE 1
First Name Last Name Salary Exact First Name MIA
Emily Smith $64,901 0 Salary $188,657 VLOOKUP is case insensitive t
James Anderson $70,855 FALSE =VLOOKUP(G2,B3:D9,3,FALSE()) case are same or doesn't matt
MIA Clark $188,657 FALSE $70,855 Index match can solve that w
John Lewis $97,566 FALSE 188657
Jessica Walker $58,339 FALSE 188657
Mia Reed $125,180 TRUE q1. Mia
Richard Lopez $91,632 FALSE 6 q1. What is salary of Mia ?
188657
Solving the Limitation of VLOOKUP 125180 {=INDEX(D3:D9,MATCH(TRUE(),EXACT(G8,B3:B9))
index+Match+Exact- When vlookup is not able to understand Upppercase or Lowercse
125180 {=INDEX(D3:D9,MATCH(TRUE(),EXACT(G8,B3:B9),0))} [Link]

Vlookup is
not working
its getting
the salary
of MIA. But
we are
looking for
Mia 188657 =VLOOKUP(B14,$B$3:$D$9,3,0) Mia.
This is the
salary
125180 of Mia [Link]
{=INDEX($D$3:$D$9,MATCH(TRUE(),EXACT(B14,$B$3:$B$9),0))}
OKUP is case insensitive that is Upper case and lower
are same or doesn't matter.

x match can solve that with the help of Exact function.

salary of Mia ?

TRUE(),EXACT(G8,B3:B9)))}

[Link]/examples/[Link]

[Link]/tutorials/excel-tutorials/vlookup-column-and-row/
 Vlookup+two way lookup(one dimensional)
Index+Match+&+array

One dimensional -Index+MATch+MATCH if you are looking for Two Values

First Name Last Name Salary First Name James
James Smith $64,901 Last Name Anderson
James Anderson $70,855 Salary $70,855
John Lewis $97,566 What is the salalry of James Anderson?
John Walker $58,339 Lookup 1 James
Mark Reed $125,180 Lookup2 Anderson
Richard Lopez $91,632 70855

[Link]

How to solve the problem of Vlookup q1. Lewis

Salary 97566
q1. what is the salary of Lewis?

Q2. What is the salary of James Clark? q2. James

Smith
Salary 64901
Case sensitive Look up
[Link]
Index+Match(true,Exact)+Array Formula

First Name Last Name Salary First Name MIA

Emily Smith $64,901 Salary $125,180 {=INDEX(D6:D12,MATCH(TRUE(),EXACT(
James Anderson $70,855
Mia Clark $188,657 Salary of MIA 125180
John Lewis $97,566 125180
Jessica Walker $58,339
MIA Reed $125,180
Richard Lopez $91,632

Functions Format
TRUE
Exact
Index
Match
Array Formula
12,MATCH(TRUE(),EXACT(G5,B6:B12),0))}
First NameLast NameSalary
Emily Smith $64,901
James Anderson $70,855 [Link] is the positon of Lewis in the dataset?
Mia Clark $188,657 4 4
John Lewis $97,566 [Link] is the value of 4th row and 1st column?
Jessica Walker $58,339 John
MIA Reed $125,180 Q3. What is the first name of the last name Lewis?
Richard Lopez $91,632 Lookup value, Last Name Lewis
Required value, First name ?
Vlookup will work? No, Left Lookup
INDEX+MATCH,
John

Q4. What is the first name of the person who earned salary $91632?
Richard Lopez last name of the person who earned 91632
Richard
 Q5. What is the last name of MIA?
Reed

salary $91632?
son who earned 91632