VLOOKUP or something else ???

Question

I am trying to create a lookup based on years of service and performance.&nbsp; For example, I have data on employees that includes years of service and whether their performance is above average, average, or below average.&nbsp; I want to search additional tables of data based on that to pull a salary number.employee tableJane Doe &nbsp;&nbsp; 20 &nbsp;&nbsp; above averageJohn Doe &nbsp;&nbsp; 15 &nbsp;&nbsp; below average&nbsp;salary tableyears &nbsp;&nbsp; lower quartile &nbsp; &nbsp;&nbsp; median &nbsp; &nbsp; upper quartile15 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 45000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5500020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 55000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 60000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 65000&nbsp;What formula would I use to extract:for Jane &nbsp;&nbsp; 65000for John &nbsp; 45000&nbsp;&nbsp;

sergeibaklan · Answer

BJTMP&nbsp;
For such sample

it could be
=INDEX($D$8:$F$9,MATCH($C3,$C$8:$C$9,1),LOOKUP(D3,{"above average","average","below average"},{3,2,1}))

bjtmp · Answer

SergeiBaklan&nbsp;&nbsp;Thanks! &nbsp;

sergeibaklan · Answer

BJTMP&nbsp;, you are welcome

bjtmp · Answer

SergeiBaklan&nbsp;&nbsp;Could I chart this data based on my original example?&nbsp; I would like to see:Line chart where X axis is annual salary, Y axis is years workedFirst line is Upper QuartileSecond is MedianThird is lower&nbsp;

sergeibaklan · Answer

BJTMP&nbsp;
If I understood correctly

&nbsp;

Forum Discussion

VLOOKUP or something else ???

5 Replies

Resources