Vlookup With Conditions

C

Candyland25

New Member
Joined
Dec 2, 2016
Messages
33
Hello,

I am trying to lookup a data from table 1 to table 2 however, the vlookup is not matching locations and picking up the first role it finds: See below:
Column A Column B Column C Column D Column E Column F Column G Column H
[TABLE="width: 638"]
<colgroup><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Austin[/TD]
[TD="colspan: 3"]Austin, Hill[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]75th[/TD]
[TD]80th[/TD]
[TD]90th[/TD]
[TD]75th[/TD]
[TD]80th[/TD]
[TD]90th[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Program Analyst, Expert[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]135000[/TD]
[TD="align: right"]135500[/TD]
[TD="align: right"]125200[/TD]
[TD="align: right"]135300[/TD]
[TD="align: right"]135900[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD="align: right"]145000[/TD]
[TD="align: right"]155000[/TD]
[TD="align: right"]155500[/TD]
[TD="align: right"]145100[/TD]
[TD="align: right"]155500[/TD]
[TD="align: right"]155900[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Analyst, Senior[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]135000[/TD]
[TD="align: right"]135500[/TD]
[TD="align: right"]125500[/TD]
[TD="align: right"]135000[/TD]
[TD="align: right"]135900[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Business Analyst[/TD]
[TD="align: right"]115000[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]120500[/TD]
[TD="align: right"]115200[/TD]
[TD="align: right"]120500[/TD]
[TD="align: right"]120700[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD="align: right"]149200[/TD]
[TD="align: right"]157300[/TD]
[TD="align: right"]157500[/TD]
[TD="align: right"]149500[/TD]
[TD="align: right"]157000[/TD]
[TD="align: right"]157500[/TD]
[/TR]
</tbody>[/TABLE]

Column A Column B Column C Column D
[TABLE="width: 300"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 382"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Percentile [/TD]
[TD] Range
[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Program Analyst, Expert[/TD]
[TD]75th[/TD]
[TD="align: right"]125000[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD]90th[/TD]
[TD="align: right"]155900[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Analyst, Senior[/TD]
[TD]75th[/TD]
[TD="align: right"]125500[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Business Analyst[/TD]
[TD]80th[/TD]
[TD="align: right"]120500[/TD]
[/TR]
[TR]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD]80th[/TD]
[TD="align: right"]155500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is my formula for Table two cell (125000)

=IF(AND($C15=$C$3,$A15=$C$2),VLOOKUP($B15,$B$2:$H$8,2,0),IF(AND($C15=$D$3,$A15=$C$2),VLOOKUP($B15,$B$2:$H$8,3,0),IF(AND($C15=$E$3,$A15=$C$2),VLOOKUP($B15,$B$2:$H$8,4,0),IF(AND($C15=$F$3,$A15=$F$2),VLOOKUP($B15,$B$2:$H$8,5,0),IF(AND($C15=$G$3,$A15=$F$2),VLOOKUP($B15,$B$2:$H$8,6,0),IF(AND($C15=$H$3,$A15=$F$2),VLOOKUP($B15,$B$2:$H$8,7,0)))))))

The issue is Program Manager listed with 155500 is wrong because it should be 157300 since the location is "Austin, Hill" at 80th percentile listed in table 1. How do I Vlookup to lookup Role based on Location and Percentile?

I also tried index and match formula but that didn't work.

Thank you all of your help.

Thank you
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Excel 2010[TABLE="class: grid, width: 700"]
<tbody>[TR]
[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]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Austin
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Austin, Hill
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]75th
[/TD]
[TD]80th
[/TD]
[TD]90th
[/TD]
[TD]75th
[/TD]
[TD]80th
[/TD]
[TD]90th
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Austin
[/TD]
[TD]Program Analyst, Expert
[/TD]
[TD="align: right"]125000
[/TD]
[TD="align: right"]135000
[/TD]
[TD="align: right"]135500
[/TD]
[TD="align: right"]125200
[/TD]
[TD="align: right"]135300
[/TD]
[TD="align: right"]135900
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Austin, Hill
[/TD]
[TD]Program Manager
[/TD]
[TD="align: right"]145000
[/TD]
[TD="align: right"]155000
[/TD]
[TD="align: right"]155500
[/TD]
[TD="align: right"]145100
[/TD]
[TD="align: right"]155500
[/TD]
[TD="align: right"]155900
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Austin, Hill
[/TD]
[TD]Program Analyst, Senior
[/TD]
[TD="align: right"]125000
[/TD]
[TD="align: right"]135000
[/TD]
[TD="align: right"]135500
[/TD]
[TD="align: right"]125500
[/TD]
[TD="align: right"]135000
[/TD]
[TD="align: right"]135900
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Austin, Hill
[/TD]
[TD]Business Analyst
[/TD]
[TD="align: right"]115000
[/TD]
[TD="align: right"]120000
[/TD]
[TD="align: right"]120500
[/TD]
[TD="align: right"]115200
[/TD]
[TD="align: right"]120500
[/TD]
[TD="align: right"]120700
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]Austin, Hill
[/TD]
[TD]Program Manager
[/TD]
[TD="align: right"]149200
[/TD]
[TD="align: right"]157300
[/TD]
[TD="align: right"]157500
[/TD]
[TD="align: right"]149500
[/TD]
[TD="align: right"]157000
[/TD]
[TD="align: right"]157500
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet4​

Vlookup will return the first match it finds since you have Program manager listed twice the first Program manager will always be returned. You would need to make them unique like Program manager 1 and Program manager 2
 
Last edited:
Upvote 0
Re: Vlookup/Index/Match on Multiple Criteria/Conditions

Hi

can I ask how the formula would/should determine which of the 2 rows with 'Austin Hill/Program Manager' should be used/returned (e.g. rows 2 and 5 within the table)

Thanks
 
Upvote 0
Re: Vlookup/Index/Match on Multiple Criteria/Conditions

That's the part I couldn't figure out. The if statement only verifies "Austin Hill' in Table 1 but I couldn't figure out how to get Program Manager from Austin Hill's 80th salary instead of the Austin Program Manager.
 
Upvote 0
Yes I realize its' picking up the first program manager but how i modify my formula to pick up a salary based on location and role?
 
Upvote 0
Re: Vlookup/Index/Match on Multiple Criteria/Conditions

Your posted table shows

Austin, Hill Program Manager twice there is no Austin program manager. Is one of them suppose to be Austin?
 
Last edited:
Upvote 0
Assuming the first program manager should be Austin

Excel 2010[TABLE="class: grid, width: 700"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[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]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Austin[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Austin, Hill[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]75th[/TD]
[TD]80th[/TD]
[TD]90th[/TD]
[TD]75th[/TD]
[TD]80th[/TD]
[TD]90th[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Austin[/TD]
[TD]Program Analyst, Expert[/TD]
[TD="align: right"]125,000[/TD]
[TD="align: right"]135,000[/TD]
[TD="align: right"]135,500[/TD]
[TD="align: right"]125,200[/TD]
[TD="align: right"]135,300[/TD]
[TD="align: right"]135,900[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Austin[/TD]
[TD]Program Manager[/TD]
[TD="align: right"]145,000[/TD]
[TD="align: right"]155,000[/TD]
[TD="align: right"]155,500[/TD]
[TD="align: right"]145,100[/TD]
[TD="align: right"]155,500[/TD]
[TD="align: right"]155,900[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Austin, Hill[/TD]
[TD]Program Analyst, Senior[/TD]
[TD="align: right"]125,000[/TD]
[TD="align: right"]135,000[/TD]
[TD="align: right"]135,500[/TD]
[TD="align: right"]125,500[/TD]
[TD="align: right"]135,000[/TD]
[TD="align: right"]135,900[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Austin, Hill[/TD]
[TD]Business Analyst[/TD]
[TD="align: right"]115,000[/TD]
[TD="align: right"]120,000[/TD]
[TD="align: right"]120,500[/TD]
[TD="align: right"]115,200[/TD]
[TD="align: right"]120,500[/TD]
[TD="align: right"]120,700[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD="align: right"]149,200[/TD]
[TD="align: right"]157,300[/TD]
[TD="align: right"]157,500[/TD]
[TD="align: right"]149,500[/TD]
[TD="align: right"]157,000[/TD]
[TD="align: right"]157,500[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Percentile[/TD]
[TD]Range[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Austin[/TD]
[TD]Program Analyst, Expert[/TD]
[TD]75th[/TD]
[TD="align: right"]125,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Austin[/TD]
[TD]Program Manager[/TD]
[TD]90th[/TD]
[TD="align: right"]155,500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Austin, Hill[/TD]
[TD]Program Analyst, Senior[/TD]
[TD]75th[/TD]
[TD="align: right"]125,500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]Austin, Hill[/TD]
[TD]Business Analyst[/TD]
[TD]80th[/TD]
[TD="align: right"]120,500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]Austin, Hill[/TD]
[TD]Program Manager[/TD]
[TD]80th[/TD]
[TD="align: right"]157,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet4​

[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]{=IF(A15=$C$1,INDEX($C$3:$E$7,MATCH(A15&B15,$A$3:$A$7&$B$3:$B$7,0),MATCH(C15,$C$2:$E$2,0)),INDEX($F$3:$H$7,MATCH(A15&B15,$A$3:$A$7&$B$3:$B$7,0),MATCH(C15,$F$2:$H$2,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D16[/TH]
[TD="align: left"]{=IF(A16=$C$1,INDEX($C$3:$E$7,MATCH(A16&B16,$A$3:$A$7&$B$3:$B$7,0),MATCH(C16,$C$2:$E$2,0)),INDEX($F$3:$H$7,MATCH(A16&B16,$A$3:$A$7&$B$3:$B$7,0),MATCH(C16,$F$2:$H$2,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D17[/TH]
[TD="align: left"]{=IF(A17=$C$1,INDEX($C$3:$E$7,MATCH(A17&B17,$A$3:$A$7&$B$3:$B$7,0),MATCH(C17,$C$2:$E$2,0)),INDEX($F$3:$H$7,MATCH(A17&B17,$A$3:$A$7&$B$3:$B$7,0),MATCH(C17,$F$2:$H$2,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D18[/TH]
[TD="align: left"]{=IF(A18=$C$1,INDEX($C$3:$E$7,MATCH(A18&B18,$A$3:$A$7&$B$3:$B$7,0),MATCH(C18,$C$2:$E$2,0)),INDEX($F$3:$H$7,MATCH(A18&B18,$A$3:$A$7&$B$3:$B$7,0),MATCH(C18,$F$2:$H$2,0)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD="align: left"]{=IF(A19=$C$1,INDEX($C$3:$E$7,MATCH(A19&B19,$A$3:$A$7&$B$3:$B$7,0),MATCH(C19,$C$2:$E$2,0)),INDEX($F$3:$H$7,MATCH(A19&B19,$A$3:$A$7&$B$3:$B$7,0),MATCH(C19,$F$2:$H$2,0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
1] As per Post #1 layout , change "Table1" A8, from "Austin, Hill" to "Austin"

and, change "Table2" A19, from "Austin, Hill" to "Austin"

2] In "Table2" D15, enter formula and copied down :

=INDEX(C$4:H$8,MATCH(1,INDEX((A15=A$4:A$8)*(B15=B$4:B$8),0),0),MATCH(C15,C$3:E$3,0)+MATCH(A15,C$2:H$2,0)-1)

Then, you can obtain 157,300 in D19

Regards
Bosco
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Solved
VLOOKUP or INDEX or MATCH or INDIRECT
Replies
2
Views
898
MrDB4Excel
MrDB4Excel
V
  • Question Question
Vlookup - multiple columns
Replies
3
Views
2K
Vcoppens
V
W
  • Question Question
return value based on matrix column name and row name
Replies
5
Views
659
AlanY
A
BradleyS
  • Question Question
Result from either VLOOKUP or INDEX/MATCH put under correct column
Replies
5
Views
628
Fluff
Fluff
ChristineJ
  • Question Question
Macro to replace values in a range (similar to vlookup or index/match)
Replies
7
Views
1K
PCL
PCL

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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