You are here:

# Excel/Excel 2007 Windows Formula

Question
im about a 5 on the scale.

I have a workbook that has two worksheets.  What I am looking to do is this.

Part 1

Worksheet 1 in G2 I want it to look at the # in A2 go to worksheet 2 column L and search the entire column for an exact match.  If there is a match I want it to take the date in Column N of the same row and return that date to G2 on worksheet 1.  If no match then do nothing.  I then want to copy that formula down to all of column G

Part 2

In K2 of worksheet 1 look at the # in A2.
Go to column L of worksheet 2 and search the entire column for a match.  If there is a match then look at column H of the same row and if the letters ALS appear in the text then return the letter M to K2.  If there is no match then return the letter R to K2.

In K2 of

Hi Rick,

I apologize for taking so long to answer your question. I had to work on it pieces at a time complete it.  Here is code that I believe does what you want.  You didn't mention autofilling the formula in K2 down (Part 2), but I assumed you wanted the code to do that as well.  However I annotated where this is in the code so you can comment it out or delete it if you don't want it.

Sub Matching()

'Part 1

'  Worksheet 1 in G2 I want it to look at the # in A2 go to worksheet 2
'  column L and search the entire column for an exact match.  If there
'  is a match I want it to take the date in Column N of the same row
'  and return that date to G2 on worksheet 1.  If no match then do
'  nothing.  Then copy that formula down to all of column G

Worksheets(1).Activate
Range("G2").Formula = "=IF(ISNA(VLOOKUP(A2,Sheet2!L\$1:N\$30,3,FALSE)),"""",VLOOKUP(A2,Sheet2!L\$1:N\$30,3,FALSE))"
'now copy formula down
Dim LastRow       As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G2", Cells(LastRow, "G"))

'Part 2

'  In K2 of worksheet 1 look at the # in A2.
'  Go to column L of worksheet 2 and search the entire column for a match.
'  If there is a match then look at column H of the same row and if the
'  letters ALS appear in the text then return the letter M to K2.
'  If there is no match then return the letter R to K2.

Range("K2").Formula = "=findals(A2,Sheet2!L1:L30)"
'  Comment the following line out if you don't want the formula in K2
'  to autofill down to the end of data
Range("K2").AutoFill Destination:=Range("K2", Cells(LastRow, "K"))

End Sub

Function FindALS(FindVal As Single, TableRng As Range) As String
Dim iRow       As Long
Dim ColHval    As String

For iRow = TableRng.Row To TableRng.Rows.Count
If TableRng(iRow, 1) = FindVal Then
ColHval = Sheet2.Cells(TableRng(iRow, 1).Row, "H")
If ColHval Like "*ALS*" Then
FindALS = "M"
Else
FindALS = "R"
End If
Exit For
End If
Next iRow

End Function
___________________________________________________

Note that I wrote a user-defined function (UDF) FindALS to implement Part 2 because I found that doing it with the built-in worksheet functions was inefficient and unwieldy.  Just run sub Matching to do both Part 1 and Part 2.

Let me know if you have any problems with this.

Damon
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 8 Politeness = 10 Comment Responded promptly and provided the formula. Had to tweak it a bit but it was way more than what I would have been able to do on my own.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Damon Ostrander

##### Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

##### Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.