New Contributor

# VLOOKUP or something else ???

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

5 Replies

# Re: VLOOKUP or something else ???

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}))``

Thanks!

# Re: VLOOKUP or something else ???

@BJTMP , you are welcome

# Re: VLOOKUP or something else ???

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

# Re: VLOOKUP or something else ???

If I understood correctly