Looking for matches

B

Bill C

I'm trying to look up a name within a spreadsheet where two columns must match the two search criteria and the value from the third is returned. For example: A table of 50 rows and 20 columns which the first column may have 10 matches to the first criteria and then I want to find the value in let's say the 5th column for the row from the original 10 matches which matches the name in a second search criteria (which is contained in column 7). Any ideas?
 
T

Tom Ogilvy

find the 10 matches, then check each to see if you get the second match.

--
Regards,
Tom Ogilvy


Bill C said:
I'm trying to look up a name within a spreadsheet where two columns must
Click to expand...
match the two search criteria and the value from the third is returned. For
example: A table of 50 rows and 20 columns which the first column may have
10 matches to the first criteria and then I want to find the value in let's
say the 5th column for the row from the original 10 matches which matches
the name in a second search criteria (which is contained in column 7). Any
ideas?
 
K

kkknie

If you are always searching column A for the first match and column
for the seconds, use:


Code
-------------------
Sub FindMatch()

Dim r As Range
Dim iMax As Long
Dim strReturn As String

iMax = Range("A65536").End(xlUp).Row

For Each r In Range("A1:A" & iMax)
If r.Value = "First Search Criteria" Then
If Cells(r.Row, 7).Value = "Second Search Criterea" Then
strReturn = Cells(r.Row, 10).Value 'Assumes last column is column 10
End If
End If
Next

MsgBox "Found: " & strReturn

End Su
-------------------

There's a lot of assumptions, repost to clarify if this doesn't work.

K

Note: Untested
 
Top