Search a cell text from database result at first column of the found row

[Hendry]

I thought I had post this thread but I could not find it. Anyway, Here it is

Dear Forum,

I have this data (Real Data much more than these):
Col-1 Col-2 Col-3 Col-4
23 a aa ai
45 b ba bi
60 j ja ji
35 d da di
44 H Ha Hi
67 w wa wi
Question:
Is there any functions or vba to do the followings:
If I search for "ja" (Case Sensitive) it will give me 60 as the result
Ha = 44; a = 23

Any help would be very much appreciated. Thanks, Hendry

 

[Sal Paradise]

Assuming you never repeat the same letter twice in column B, and that the pattern is always X / Xa / Xi

Excel Workbook
	A	B	C	D
1	23	a	aa	ai
2	45	b	ba	bi
3	60	j	ja	ji
4	35	d	da	di
5	44	H	Ha	Hi
6	67	w	wa	wi
7
8	ja	60
Sheet1

 

[Hendry]

Thanks for your prompt reply Sal,

My data could grow like this:
89 sh sha shi shu she sho
78 q qa qi qu qe qo
66 r ra ri ru re ro
17 sy sya syi syu sye syo
99 Q QA QI QU QE QO
If I put sya, it will give me 17; QU = 99

Please advise,

 

[Sal Paradise]

Case sensitive made it a beast, but it works:

Excel Workbook
	A	B	C	D	E	F	G
1	23	a	aa	ai
2	45	b	ba	bi
3	60	j	ja	ji
4	35	d	da	di
5	44	H	Ha	Hi
6	67	w	wa	wi
7	89	sh	sha	shi	shu	she	sho
8	78	q	qa	qi	qu	qe	qo
9	66	r	ra	ri	ru	re	ro
10	17	sy	sya	syi	syu	sye	syo
11	99	Q	QA	QI	QU	QE	QO
12
13	q	78
Sheet1

 

[Hendry]

Thank you Sal, I don't know how it works but the formula got the right answer for any word in the list. Do you mind modify it so it will return "Not Found" if the A13 is not in the database. Cheers,