Get data for one field based on what is entered in another

[kc8jpk03]

When I enter a code into E4:???, I want F4:??? to be matched against a list that I have in J4:J35, I want the corresponding data in K4:K35 to be populated in F4:F??? as appropriate. The formula below works, however when copied, it increases every field reference by one, which loses my list:

=IF(E4=J4,K4,IF(E4=J5,K5,IF(E4=J6,K6,IF(E4=J7,K7,IF(E4=J8,K8,IF(E4=J9,K9,IF(E4=J10,K10,IF(E4=J11,K11,IF(E4=J12,K12,IF(E4=J13,K13,IF(E4=J14,K14,IF(E4=J15,K15,IF(E4=J16,K16,IF(E4=J17,K17,IF(E4=J18,K18,IF(E4=J19,K19,IF(E4=J20,K20,IF(E4=J21,K21,IF(E4=J22,K22,IF(E4=J23,K23,IF(E4=J24,K24,IF(E4=J25,K25,IF(E4=J26,K26,IF(E4=J27,K27,IF(E4=J28,K28,IF(E4=J29,K29,IF(E4=J30,K30,IF(E4=J31,K31,IF(E4=J32,K32,IF(E4=J33,K33,IF(E4=J34,K34,IF(E4=J35,K35,"ERROR"))))))))))))))))))))))))))))))))

I am sure there has to be an easier way to accomplish this task.

Thank you in advance for your time and consideration.

[JohnTopley]

Try

=IFERROR(VLOOKUP(E4,$J$4:$K$23,2,0),"")

Copy down

[kc8jpk03]

Thank you! Works Great!