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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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
 

Hendry

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

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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
 

Hendry

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

Forum statistics

Threads
1,136,434
Messages
5,675,841
Members
419,586
Latest member
RoteichA

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
Top