VLOOKUP or something else ???

%3CLINGO-SUB%20id%3D%22lingo-sub-1544109%22%20slang%3D%22en-US%22%3EVLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544109%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20lookup%20based%20on%20years%20of%20service%20and%20performance.%26nbsp%3B%20For%20example%2C%20I%20have%20data%20on%20employees%20that%20includes%20years%20of%20service%20and%20whether%20their%20performance%20is%20above%20average%2C%20average%2C%20or%20below%20average.%26nbsp%3B%20I%20want%20to%20search%20additional%20tables%20of%20data%20based%20on%20that%20to%20pull%20a%20salary%20number.%3C%2FP%3E%3CP%3Eemployee%20table%3C%2FP%3E%3CP%3EJane%20Doe%20%26nbsp%3B%26nbsp%3B%2020%20%26nbsp%3B%26nbsp%3B%20above%20average%3C%2FP%3E%3CP%3EJohn%20Doe%20%26nbsp%3B%26nbsp%3B%2015%20%26nbsp%3B%26nbsp%3B%20below%20average%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esalary%20table%3C%2FP%3E%3CP%3Eyears%20%26nbsp%3B%26nbsp%3B%20lower%20quartile%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20median%20%26nbsp%3B%20%26nbsp%3B%20upper%20quartile%3C%2FP%3E%3CP%3E15%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2045000%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2050000%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2055000%3C%2FP%3E%3CP%3E20%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2055000%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2060000%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2065000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20formula%20would%20I%20use%20to%20extract%3A%3C%2FP%3E%3CP%3Efor%20Jane%20%26nbsp%3B%26nbsp%3B%2065000%3C%2FP%3E%3CP%3Efor%20John%20%26nbsp%3B%2045000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1544109%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544311%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739143%22%20target%3D%22_blank%22%3E%40BJTMP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207699iA1C0967E32A88FCF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24D%248%3A%24F%249%2CMATCH(%24C3%2C%24C%248%3A%24C%249%2C1)%2CLOOKUP(D3%2C%7B%22above%20average%22%2C%22average%22%2C%22below%20average%22%7D%2C%7B3%2C2%2C1%7D))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544348%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544651%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739143%22%20target%3D%22_blank%22%3E%40BJTMP%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548791%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20I%20chart%20this%20data%20based%20on%20my%20original%20example%3F%26nbsp%3B%20I%20would%20like%20to%20see%3A%3C%2FP%3E%3CP%3ELine%20chart%20where%20X%20axis%20is%20annual%20salary%2C%20Y%20axis%20is%20years%20worked%3C%2FP%3E%3CP%3EFirst%20line%20is%20Upper%20Quartile%3C%2FP%3E%3CP%3ESecond%20is%20Median%3C%2FP%3E%3CP%3EThird%20is%20lower%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549033%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20or%20something%20else%20%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739143%22%20target%3D%22_blank%22%3E%40BJTMP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20correctly%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20487px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208327i3B71260821D323F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

@BJTMP 

For such sample

image.png

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

@Sergei Baklan 

 

Thanks!  

Highlighted

@BJTMP , you are welcome

Highlighted

@Sergei Baklan 

 

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 

Highlighted

@BJTMP 

If I understood correctly

image.png