Formula: Search value in range and return another value on same row

[vabtroni]

Hello everyone, good evening!

I have a table on wich I organize the tasks assigned to a given fleet of vehicles during the day. For example, vehicle 100 will perform the tasks "S32", "SW3" and "NE32". These are random names, no speacial meaning on any of them.

On the right, on range L2:M3 I have a little "search box" on wich I insert a task name on M2, and I need a formula on M3 that returns the vehicle that is performing that task. For example, if I write "S10" on M2, M3 should display "102", meaning that vehicle 102 is performing task "S10".

Could anyone shed some light on this one?

Best regards,
V

 

[Legacy 143009]

=INDEX($B$1:$B$7,AGGREGATE(15,6,ROW($B$2:$B$7)/($B$2:$E$7=$M$2),1))

 

[Peter_SSs]

=INDEX($B$1:$B$7,AGGREGATE(15,6,ROW($B$2:$B$7)/($B$2:$E$7=$M$2),1))

I would say that formula is a bit 'risky' (apart from the range B2:E7 not being wide enough). If any new rows are subsequently added above the data it will return incorrect result.
It could easily be made more robust as follows. I have also increased the width of the scanned range in line with the image in post 1.

23 01 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Vehicle #	Tasks										S10
3		100	S32	SW3	NE32								102
4		101	NE4	NW34
5		102	DTWN	IND	S10	S11
6		103	401
7		104	SW91	SE11	S900
Lookup Vehicle

Cell Formulas
Range	Formula
M3	M3	=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$7)/($B$2:$J$7=$M$2),1))

 

[Legacy 143009]

I would say that formula is a bit 'risky' (apart from the range B2:E7 not being wide enough).

Sorry, my test range left there. Apoligies

 

[shinigamilight]

=INDEX($B$1:$B$7,MAX(ROW($B$3:$B$7)*--($M$2=$C$3:$J$7)))

 

[Peter_SSs]

=INDEX($B$1:$B$7,MAX(ROW($B$3:$B$7)*--($M$2=$C$3:$J$7)))

Exactly the same problem as post #2 formula if rows are subsequently added at the top.
(BTW, "--" is not required in the formula)

 

[vabtroni]

Thank you all for your answers. Will try the formula as soon as possible.

However, I can assure you all one thing: the workspace is heavily protected, so there is no chance that the user can add or delete rows while using the sheet.

Will feedback later today. Cheers everyone!

 

[Peter_SSs]

the workspace is heavily protected, so there is no chance that the user can add or delete rows while using the sheet.

Still, in my view, good practice to get into the habit of using formulas that are as robust as possible, particularly if they are no more complex than the alternative.