Vlookup With Conditions

[Candyland25]

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 1
		Austin	Austin, Hill
		75th	80th	90th	75th	80th	90th
Austin	Program Analyst, Expert	125000	135000	135500	125200	135300	135900
Austin, Hill	Program Manager	145000	155000	155500	145100	155500	155900
Austin, Hill	Program Analyst, Senior	125000	135000	135500	125500	135000	135900
Austin, Hill	Business Analyst	115000	120000	120500	115200	120500	120700
Austin, Hill	Program Manager	149200	157300	157500	149500	157000	157500

<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

Column A Column B Column C Column D

Table 2 Percentile Range Austin Program Analyst, Expert 75th 125000 Austin, Hill Program Manager 90th 155900 Austin, Hill Program Analyst, Senior 75th 125500 Austin, Hill Business Analyst 80th 120500 Austin, Hill Program Manager 80th 155500 <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

 

[Scott T]

Excel 2010

	A	B	C	D	E	F	G	H
1			Austin			Austin, Hill
2			75th	80th	90th	75th	80th	90th
3	Austin	Program Analyst, Expert	125000	135000	135500	125200	135300	135900
4	Austin, Hill	Program Manager	145000	155000	155500	145100	155500	155900
5	Austin, Hill	Program Analyst, Senior	125000	135000	135500	125500	135000	135900
6	Austin, Hill	Business Analyst	115000	120000	120500	115200	120500	120700
7	Austin, Hill	Program Manager	149200	157300	157500	149500	157000	157500

<tbody>
</tbody>

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

 

[Candyland25]

Re: Vlookup/Index/Match on Multiple Criteria/Conditions

I tired vlookup and match but couldn't get it right.

 

[edmitchell]

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

 

[Candyland25]

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.

 

[Candyland25]

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?

 

[Scott T]

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?

 

[Scott T]

Assuming the first program manager should be Austin

Excel 2010

	A	B	C	D	E	F	G	H
1			Austin			Austin, Hill
2			75th	80th	90th	75th	80th	90th
3	Austin	Program Analyst, Expert	125,000	135,000	135,500	125,200	135,300	135,900
4	Austin	Program Manager	145,000	155,000	155,500	145,100	155,500	155,900
5	Austin, Hill	Program Analyst, Senior	125,000	135,000	135,500	125,500	135,000	135,900
6	Austin, Hill	Business Analyst	115,000	120,000	120,500	115,200	120,500	120,700
7	Austin, Hill	Program Manager	149,200	157,300	157,500	149,500	157,000	157,500
8
9
10
11
12
13
14	Percentile	Range
15	Austin	Program Analyst, Expert	75th	125,000
16	Austin	Program Manager	90th	155,500
17	Austin, Hill	Program Analyst, Senior	75th	125,500
18	Austin, Hill	Business Analyst	80th	120,500
19	Austin, Hill	Program Manager	80th	157,000

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula D15 {=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)))} D16 {=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)))} D17 {=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)))} D18 {=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)))} D19 {=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)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[bosco_yip]

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