Search with Match and Index (2002/2003)

Topic

New Reply

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!

Reply | Quote

Replies

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)))

Reply | Quote

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

Reply | Quote

Thanks, that’s shorter and clearer.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 D3:D25)

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

Reply | Quote