Formula to return specific text based on look up value

[pbj1688]

HI, I have two spreadsheets. First sheet has the item code for look up, second sheet has the item code with associated value. I need help to figure out a formula that would search for the item code, reference the associated value, to then return specified text based on the value. Any help would be appreciated!

Book1
	A	B	C	D	E	F	G	H	I	J
1	lookup sheet			data sheet
2	Code			Code	Items				value	specific text to return
3	141414			141414	Apple				Apple	train
4	123456			123456	Appke, Orange, Strawberry				Appke, Orange, Strawberry	car
5	583211			583211	Apple, Orange				Apple, Orange	airplane
6	590283			590283	Orange, Strawberry				Orange, Strawberry	submarine
7	385692			385692	Strawberry				Strawberry	taxi
Sheet1

 

[KOKOSEK]

Like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	lookup sheet			data sheet
2	Code			Code	Items				value	specific text to return
3	141414	train		141414	Apple				Apple	train
4	123456	car		123456	Appke, Orange, Strawberry				Appke, Orange, Strawberry	car
5	583211	airplane		583211	Apple, Orange				Apple, Orange	airplane
6	590283	submarine		590283	Orange, Strawberry				Orange, Strawberry	submarine
7	385692	taxi		385692	Strawberry				Strawberry	taxi
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	=VLOOKUP(VLOOKUP(A3,D:E,2,0),I:J,2,0)

 

[pbj1688]

Thanks so much for the quick reply! Column I and J are actually not on the spreadsheet. I was trying to do a combination of xlookup and IF statements so that if E3 = "Apple" then the response would be "train" without having a lookup table. Is that possible?