=IF(ISNUMBER(SEARCH

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Hi guys,

Using a formula at the moment as:

=IF(ISNUMBER(SEARCH(H8,R24)),H8,"")

Essentially I want to search for contents of H8 in cell R24, and if found, return H8. This formula works but I would like the search to be conducted from right to left? I believe at the moment I will sarch for H8 value from left to right in cell R24...

Thanks
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
Do you mean that if "TOM" is found in cell H8, then it should search cell R24 for "MOT" and not "TOM"?
 

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Not quite, if TOM is found in H8, it should TOM in R24, but start the search from right to left of the cell. Because my formula will have multiple searches back to back. Aka my search criteria may be, TOM, JIM, JED.... I will be search for all three, but if in R24 the data is TOMJIMJED, I need to search for the one closest to end, ie right to left.... Thanks
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
Let's say you have "TOMJIMJED" in R24 and H8 is TOM. Then, irrespective of the direction of the search the match will be found and H8 i.e. TOM will be the return value.
Similarly, if you have "TOMJIMJED" in R24 and H8 is JED. Then, irrespective of the direction of the search the match will be found and H8 i.e. JED will be the return value.
Can you give an example wherein the direction of the search will influence the output?
 

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Yes your correct, but I will be cycling through searches, and lets say TOMJIMJED is in the cell, and I am cycling through the search criteria's of TOM, TIM, JED, I need the search to find JED, as this is furthest right... If TOM is searched before jed, this will be returned but I need the furthest right result...
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
I would like you to give an sample of your data in cells H8 and R24 for which you think the direction will influence the output.
Or do you want to say that you have data in cell H8 and R24 where multiple names match? e.g. TOMJIMJED. So far I am guessing that H8 would a single name all the time and R24 would be multiple names.
 

Forum statistics

Threads
1,081,996
Messages
5,362,612
Members
400,684
Latest member
Vie

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top