WS Function 1

[joemajestee]

Is there a worksheet function that can find a text string if it's in a range?

=somefunction("pdf",A1:C1) returns true or false
if I put the formula into d1 and copy down, it should look like this:

xxx    A    B    C    D
 1   mary  jack  pdf  True
 2   pdf   nut   jpg  True
 3   not   here       False
 4   PDF   not   not  True
 5   apdf  not   h    True

Thanks, Joe

 

[SkipVought]

Hi,

Check out the MATCH function. It only works on a SINGLE ROW or COLUMN range, so you would need to code the function for each ROW or COLUMN of interest.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

=NOT(ISNA(MATCH("pdf",A1:C1,0)))

copied down.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[joemajestee]

Thanks!!!
But wait, there's more.

Instead of looking in A1:C1, I would like to point it to a vlookup, if that's appropriate, but I'm having trouble.

I want it to look in a single row, columns Q:T on sheet "Master" where the value in cell two to the right of the cell containing the formula matches the value in the first column in Master.

So
in cell D1, =NOT(ISNA(MATCH("pdf",=what,0)))

=what is =what(F1,Master!$A$2:$Z$100,Q through T,...)

 

[SkipVought]

what(F1,Master!$A$2:$Z$100,Q through T,...)

please explain what that is supposed to mean in plain words.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joemajestee]

what" is the function that I hope exists and I'm guessing about it's syntax.

so =what(arg1,arg2,arg3,arg4)

This mythical formula would be in D1.

arg1 in my example is a cell reference, so maybe it is the value "MyCategory", in F1.

arg2 in my example is a table which contains Categories in column A

arg3 dictates in which columns to search, in my example I'm columns Q through T of the appropriate row.

The appropriate row is determined by matching the value in F1 to a unique value in Categories. So if "MyCategory" appears in Master!A7, then I want to look for "pdf" in the range "Q7:T7"

I hope I'm clear enough and thanks again. There is no argument 4 yet, but I was guessing there would be in the real formula like there is in the other lookup functions I've used.

 

[Gruuuu]

Really not sure what you're trying to accomplish here. What's the end result of what you're looking for?

 

[GlennUK]

=NOT(ISNA(MATCH("pdf",OFFSET(Master!$Q$1,MATCH(F1,Master!$A$1:$A$100,0)-1,0,1,4),0)))

as a guess.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[joemajestee]

Thanks for your help. I tried yours Glenn and it didn't work, although I'm not sure how it's supposed to work so I may have provided the wrong arguments.

I've tried to write a function, but it doesn't work either. On the FindNext line, it does not actually find the next, it sets FoundIt to nothing and exits, although there is indeed more than just the first instance of SearchFor.

=FindInTable($L$2,P3,MASTER!$A$2:$A$86,MASTER!I:S)

Private Function FindInTable(SearchFor, InCategory, InTable As Range, TargetColumns As Range)
    Dim Categories As Range, FoundIt As Range, LCol As Integer, firstAddress
    FindInTable = "True"
    LCol = Cells.Find(what:="*", after:=[A1], searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    Set Categories = InTable.Find(what:=InCategory, searchdirection:=xlNext)
    If Categories Is Nothing Then
        FindInTable = "Error, No Such Category"
        Exit Function
    End If
    Set FoundIt = TargetColumns.Find(what:=SearchFor, searchdirection:=xlNext)
    If FoundIt Is Nothing Then
        FindInTable = "False"
        Exit Function
    End If
    firstAddress = FoundIt.Address
    Do
        If FoundIt.Row = Categories.Row Then Exit Function
        Set FoundIt = TargetColumns.FindNext(SearchFor)
    Loop While Not FoundIt Is Nothing And FoundIt.Address <> firstAddress
    If FoundIt Is Nothing Then FindInTable = "False"
End Function

 

[SkipVought]

Holy Macro! You're making things more complicater 'n' complicator!!!

You've got this hairy function with FOUR arguments.

You look in InTable to find LCol, and never use it!!!

You look in TargetColumns.

You have

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Holy Macro! You're making things more complicater 'n' complicator!!!

Let's go by the numbers.

ROW 1 has some CATEGORIES?

So you want to lookup a CATEGORY then in the COLUMN for that CATEGORY, you want to lookup a value?

[TOTAL DATA RANGE]: in my example B24
F2 lookup val
G1 lookup category
[tt]
=INDEX([TOTAL DATA RANGE],MATCH([LOOKUP VAL],OFFSET(A1,1,MATCH(G1,B11,0),COUNTA(B:B)-1,1),0),MATCH(G1,B11,0))
[/tt]
returns N/A if no match


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joemajestee]

Skip,

You are right about the LCol, it's left over from a previous attempt that also failed. I'm laughing at your response because I thought it kinder than mine would have probably been. :-D