Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA Vlookup function

Posted on 2013-11-18
9
Medium Priority
?
567 Views
Last Modified: 2013-11-19
I am trying to find a value in a spreadsheet table. I am wondering if Vlookup may be the correct function to use. I get a value such as "GQ-000" from a worksheet called "DRA Summary" and would like to find that value in another worksheet called "Data" within the same workbook.
Do you think this may be the best way to do this?
Any suggestions and examples of how Vlookup works would be greatly appreciated.
Thanks in advance.
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39656341
If it is 'the best way' to do it, depends on what it is exactly you are trying to accomplish. Vlookup looks for a value in the leftmost column in the specified range and returns the value in the same row from the column you specify. It returns a #N/A if the value is not found.

A simple way if you just want to check a value exists in the other worksheet, is:
=CountIf("Data" range,lookup value)

Open in new window


Joop
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39656355
If
your value is in A1 of DRA Summary and
your data list is in column A of data

then
This function will give you the row number in the data sheet

=match('DRA Summary'!A1,data!A:A,0)
0
 

Author Comment

by:tesla764
ID: 39656539
Here is what I am trying to accomplish...

Look at Module "Get_SQnumber2"
The variable "s" contains the value to be looked up in the sheet "Data".
Once that value is found I want to move the value in column "E" (Data) sheet to Row 2 column "F" (DRA Summary) sheet.

The code module is below and I also attached the work book "Data.xlsm"

Sub Get_SQnumber2()
    Dim rng As Range, cell As Range
    Dim lc As Integer
    Dim s As String, sInput As String
   
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(3, 1), Cells(3, lc))
   
    For Each cell In rng
        If cell.Value Like "SQ-*" Then
            'MsgBox cell.Value
            'The variable s has the value to be looked up.
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            MsgBox "s: " & s
        End If
    Next
Data.xlsm
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 6

Expert Comment

by:Michael
ID: 39657187
Maybe like this:
Sub Get_SQnumber2()
    Dim rng As Range, cell As Range
    Dim lc As Integer
    Dim s As String, sInput As String
    
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(3, 1), Cells(3, lc))
    
    'Set lookup range
    Set LkupRng = Sheets("Data").Range("A1").CurrentRegion
    
    For Each cell In rng
        If cell.Value Like "SQ-*" Then
            MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)
            MsgBox "s: " & s

            On Error Resume Next
            v = WorksheetFunction.VLookup(s, LkupRng, 5, 0)
            Sheets("DRA Summary").Range("F2") = v
            End
        End If
    Next
End Sub

Open in new window

If it finds the SQ-*** value in column A of the 'Data' worksheet it returns the value in column E of the same row to cell F2 of the 'DRA Summary' worksheet, and then ends the procedure.

Joop
0
 

Author Comment

by:tesla764
ID: 39658703
I've requested that this question be closed as follows:

Accepted answer: 0 points for tesla764's comment #a39656539

for the following reason:

Thanks, this gives me something I can work with. I still need to make a few more mods, but you have been very helpful. This is the first time I have used Vlookup.
Thanks again.
0
 
LVL 6

Accepted Solution

by:
Michael earned 2000 total points
ID: 39658704
0
 

Author Closing Comment

by:tesla764
ID: 39660120
No 500points are deserved. I don't know why 0 showed up. Sorry about that.
0
 

Author Comment

by:tesla764
ID: 39660196
JazzyJoop did you get the 500 points I assigned to you?
0
 
LVL 6

Expert Comment

by:Michael
ID: 39660235
Yes sir, thank you! :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question