Jul 24 2020 09:30 AM
I am trying to create a lookup based on years of service and performance. For example, I have data on employees that includes years of service and whether their performance is above average, average, or below average. I want to search additional tables of data based on that to pull a salary number.
employee table
Jane Doe 20 above average
John Doe 15 below average
salary table
years lower quartile median upper quartile
15 45000 50000 55000
20 55000 60000 65000
What formula would I use to extract:
for Jane 65000
for John 45000
Jul 24 2020 11:05 AM
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}))
Jul 27 2020 01:35 PM
Could I chart this data based on my original example? I would like to see:
Line chart where X axis is annual salary, Y axis is years worked
First line is Upper Quartile
Second is Median
Third is lower
Jul 27 2020 04:02 PM