Vlookup with 2 Criterias

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all..

how to lookup data with 2 criteria:
this layout

yearnamescore
2017johny100
2017andy150
2017mike200
2018johny300
2018andy400
2018mike600

<tbody>
</tbody>

year
2017--johny ---?
2018 -johny--- ?

any help, thanks in advance..

.sst
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try this

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >year</td><td >name</td><td >score</td><td > </td><td >year</td><td >name</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2017</td><td >johny</td><td style="text-align:right; ">100</td><td > </td><td style="text-align:right; ">2017</td><td >johny</td><td style="text-align:right; ">100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2017</td><td >andy</td><td style="text-align:right; ">150</td><td > </td><td style="text-align:right; ">2018</td><td >johny</td><td style="text-align:right; ">300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2017</td><td >mike</td><td style="text-align:right; ">200</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2018</td><td >johny</td><td style="text-align:right; ">300</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2018</td><td >andy</td><td style="text-align:right; ">400</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2018</td><td >mike</td><td style="text-align:right; ">600</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=SUMPRODUCT(($A$2:$A$7=E2)*($B$2:$B$7=F2)*($C$2:$C$7))</td></tr><tr><td >G3</td><td >=SUMPRODUCT(($A$2:$A$7=E3)*($B$2:$B$7=F3)*($C$2:$C$7))</td></tr></table></td></tr></table>
 
Upvote 0
hi ..thank you, but i'm sorry i'm looking for using VLOOKUP FUNCTION, IT'S POSSIBLE?
 
Upvote 0
To use vlookup, maybe something like this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:96px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:99.8px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">year</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">year & name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">score</td><td style="background-color:#ffff00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">year</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">year & name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">score</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2017</td><td >johny</td><td >2017johny</td><td style="text-align:right; ">100</td><td > </td><td style="text-align:right; ">2017</td><td >johny</td><td >2017johny</td><td style="text-align:right; ">100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2017</td><td >andy</td><td >2017andy</td><td style="text-align:right; ">150</td><td > </td><td style="text-align:right; ">2018</td><td >johny</td><td >2018johny</td><td style="text-align:right; ">300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2017</td><td >mike</td><td >2017mike</td><td style="text-align:right; ">200</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2018</td><td >johny</td><td >2018johny</td><td style="text-align:right; ">300</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2018</td><td >andy</td><td >2018andy</td><td style="text-align:right; ">400</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2018</td><td >mike</td><td >2018mike</td><td style="text-align:right; ">600</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=A2&B2</td></tr><tr><td >H2</td><td >=F2&G2</td></tr><tr><td >I2</td><td >=VLOOKUP(H2,$C$2:$D$7,2,0)</td></tr></table></td></tr></table>
 
Upvote 0
Here is a round about way to do it.
Excel Workbook
ABCDEFG
1yearnamescoreYearName
22017johny1002017johny100
32017andy1502018johny300
42017mike200
52018johny300
62018andy400
72018mike600
Sheet
 
Upvote 0
You're welcome. Glad we could help. Thanks for the feedback.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top