Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search with Match and Index (2002/2003)

    In the attached sample I have two cells for search parameters: a site code to tell my formula which site column to looked through, and a budget code that needs to be validated from that selected column. If the search finds a matching budget code in the column it should echo back that valid budget code in cell B4. If it cannot find the budget code it would return "Error." Some of the data has strike-through formatting which can be ignored but should still be returned to B4 if it matched the users parameter.

    I thought a match and index function/formula would be a good place to start, but I can't seem to get the syntax right. Any suggestions? Would a SUMPRODUCT be better? Thank you!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search with Match and Index (2002/2003)

    Try this:

    =IF(ISNA(MATCH(A3,C2:E2,0)),"Invalid Site Code",IF(ISNA(VLOOKUP(B3,OFFSET(B6:B25,0,MATCH(A3, C2:E2,0)),1,FALSE)),"Invalid Budget Code",VLOOKUP(B3,OFFSET(B6:B25,0,MATCH(A3,C2:E2,0) ),1,FALSE)))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search with Match and Index (2002/2003)

    How about:
    =IF(ISNUMBER(MATCH(B3,OFFSET(B2,1,MATCH(A3,C2:E2,0 ),23,1),0)),B3,"Error")

    MATCH(A3,C2:E2,0)
    Will return the column number (1,2, or 3) if the "Site code is found". If not it will return #NA error. In your example it is 2

    OFFSET(B2,1,MATCH(A3,C2:E2,0),23,1)
    Defines a range. The range starts in the cell 1 cell down from B2, the "matched" columns over, it is 23 rows long and 1 column wide In your example this is D325)

    MATCH(B3,OFFSET(B2,1,MATCH(A3,C2:E2,0),23,1),0)
    Looks for a match of B3 (the budget code to verify) in that range and returns the index value (4). If a match of B3 is not found or if the match of A3 is not found in SIte, it will returnn #NA error

    ISNUMBER(MATCH(B3,OFFSET(B2,1,MATCH(A3,C2:E2,0),23 ,1),0))
    Determines if the "match" is a number (in your example it is true. If a #NA error was returned for the match this will return false.

    =IF(ISNUMBER(MATCH(B3,OFFSET(B2,1,MATCH(A3,C2:E2,0 ),23,1),0)),B3,"Error")

    If a match is found, it gives the budget code, if not returns an "Error"



    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search with Match and Index (2002/2003)

    I like indicating which item was invalid (which I did not do). I would go with the shorter and avoid a lookup:

    =IF(ISNA(MATCH(A3,C2:E2,0)),"Invalid Site Code",IF(ISNUMBER(MATCH(B3,OFFSET(B2,1,MATCH(A3,C2 :E2,0),23,1),0)),B3,"Invalid Budget Code"))

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search with Match and Index (2002/2003)

    Thanks, that's shorter and clearer. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search with Match and Index (2002/2003)

    Thank you Hans and Steve! I also appreciate the explanation of what's going on in the formula. That will help in the future.

    I know I said to ignore the strikethrough cell formatting when a match is found, but can it be returned in cell B4 as well?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search with Match and Index (2002/2003)

    A formula can only be used to look up a cell's value, not its formatting. You'd need to use a macro if you wanted to copy the strikeout formatting.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search with Match and Index (2002/2003)

    Here is code you could use. It's a worksheet event procedure that goes into the worksheet module (right-click the worksheet tab and select View Code from the popup menu to open this module).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varIndex As Variant
    Dim rng As Range
    If Not Intersect(Range("A3:B3"), Target) Is Nothing Then
    Application.EnableEvents = False
    varIndex = Application.Match(Range("A3"), Range("C2:E2"), 0)
    If IsError(varIndex) Then
    Range("B4") = "Invalid Site Code"
    Else
    Set rng = Range("B6:B25").Offset(0, varIndex).Find(What:=Range("B3"), _
    LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If rng Is Nothing Then
    Range("B4") = "Invalid Budget Code"
    Else
    Range("B4") = rng
    Range("B4").Font.Strikethrough = rng.Font.Strikethrough
    End If
    End If
    Application.EnableEvents = True
    End If
    End Sub

    See attached version of the workbook
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •