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
<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>
Column A Column B Column C Column D
<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
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
<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