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

Hendry

New Member
Joined
Mar 6, 2009
Messages
6
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Assuming you never repeat the same letter twice in column B, and that the pattern is always X / Xa / Xi

Excel Workbook
ABCD
123aaaai
245bbabi
360jjaji
435ddadi
544HHaHi
667wwawi
7
8ja60
Sheet1
 
Upvote 0
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,
 
Upvote 0
Case sensitive made it a beast, but it works:
Excel Workbook
ABCDEFG
123aaaai
245bbabi
360jjaji
435ddadi
544HHaHi
667wwawi
789shshashishushesho
878qqaqiquqeqo
966rrarirurero
1017sysyasyisyusyesyo
1199QQAQIQUQEQO
12
13q78
Sheet1
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,216,126
Messages
6,129,016
Members
449,480
Latest member
yesitisasport

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top